Split a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Split a string

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-01-11 : 21:13:40

Hi,

I have a MS SQL column containing a string that i would like to split. I would like to create 2 separate column for the split string.

Below is a sample data. including the result that i would like to do in my string (RESULT1 and RESULT2) column. Thank you in advance.


drop table #sample

Create table #sample
(Item nvarchar(35),RESULT1 nvarchar(35), RESULT2 nvarchar(35))
Insert into #sample (Item,RESULT1, RESULT2 ) values ('PHELL3001710TRDMAXX','PHELL3001710','TRDMAXX')
Insert into #sample (Item,RESULT1, RESULT2 ) values ('TPG6002-RTR','TPG6002','TPG6002')
Insert into #sample (Item,RESULT1, RESULT2 ) values ('PHELL3001710BROIDBIO','PHELL3001710','BROIDBIO')
Insert into #sample (Item,RESULT1, RESULT2 ) values ('DHELL3001720BRDXINIBLK','DHELL3001720','BRDXINIBLK')
Insert into #sample (Item,RESULT1, RESULT2 ) values ('NOT3920-IR','NOT3920','NOT3920')
Insert into #sample (Item,RESULT1, RESULT2 ) values ('CTC2577','CTC2577','CTC2577')
Insert into #sample (Item,RESULT1, RESULT2 ) values ('PHELL3001725BRZRMCROWHT','PHELL3001725','BRZRMCROWHT')
Insert into #sample (Item,RESULT1, RESULT2 ) values ('PHELL3004430M8SIL','PHELL3004430','M8SIL')
Insert into #sample (Item,RESULT1, RESULT2 ) values ('PHELL3004410ONE','PHELL3004410','ONE')


--Select * from #sample
Select  Item,
   CASE WHEN LEN(Item ) = 7 THEN LEFT(Item ,7)
        WHEN LEN(Item ) = 11 OR LEN(Item )= 10 OR
        LEN(Item )= 9 THEN LEFT(Item ,7)  ELSE Item  END AS RESULT,
        RESULT1, RESULT2 
from #sample order by ITEM

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-01-11 : 21:42:46

I have the result for Result 1, my problem is on how to get result 2.

below is the sample query:

Select  Item,
   CASE WHEN LEN(Item ) = 7 THEN LEFT(Item ,7)
        WHEN LEN(Item ) = 11 OR LEN(Item )= 10 OR
        LEN(Item )= 9 THEN LEFT(Item ,7)  END AS RESULT,
        CASE WHEN LEN(Item ) = 7 THEN LEFT(Item ,7)
        WHEN LEN(Item ) = 11 OR LEN(Item )= 10 OR
        LEN(Item )= 9 THEN LEFT(Item ,7) ELSE LEFT(Item,CHARINDEX('%[0-9]%',Item) + 12) END AS  RESULT1,
        
        CASE WHEN LEN(Item ) = 7 THEN LEFT(Item ,7)
        WHEN LEN(Item ) = 11 OR LEN(Item )= 10 OR
        LEN(Item )= 9 THEN LEFT(Item ,7) ELSE LEFT(Item ,12) END AS  RESULT2
from #sample order by ITEM

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-01-11 : 21:47:52

Finally got the result for RESULT2. Any help to review this would be great. thanks.

Below is the code

[Code]Select Item,
CASE WHEN LEN(Item ) = 7 THEN LEFT(Item ,7)
WHEN LEN(Item ) = 11 OR LEN(Item )= 10 OR
LEN(Item )= 9 THEN LEFT(Item ,7) ELSE RIGHT(Item, LEN(Item) - 12) END AS RESULT2
from #sample order by ITEM [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources