How to split the colun - 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

General SQL Server Forums

New to SQL Server Programming

How to split the colun

Author

Topic

boreddy
Posting Yak Master

172 Posts

Posted - 2008-11-07 : 00:16:19

Hi experts

in my table one column datatype is varchar(200)
in this column user can insert data in these form
('AA1000','2008-20000' , '2009@20000'.....)

i need to split those values as shown below
example:

1. 'AA1000' i need to split this as 'AA' and '1000'

2. '2008-20000' i need to split this as '2008-' and '20000'

3. '2009@20000' i need to split this as '2009@' and '20000'

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 00:33:33

[code]DECLARE @Table table
(
ID int,
StrVal varchar(200)
)
INSERT INTO @Table
SELECT 1,'(''AA1000'',''2008-20000'' , ''2009@20000'')' union all
SELECT 2,'(''BB2050'',''2009-44000'' , ''2008@20005'')' union all
SELECT 3,'(''2009@20000'',''AA1000'',''2008-20000'')' union all
SELECT 4,'(''CC2300'',''2008-12345'' , ''2009@43555'')' union all
SELECT 5,'(''ZZ5300'',''2008-21344'' , ''2009@23554'')'

SELECT * FROM @Table

SELECT ID,LTRIM(RTRIM(Col1)) AS Col1,LTRIM(RTRIM(Col2)) AS Col2
FROM
(
SELECT t.ID,CASE WHEN CHARINDEX('-',Val) >0
THEN LEFT(REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''),CHARINDEX('-',REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''))-1)
WHEN CHARINDEX('@',Val) >0
THEN LEFT(REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''),CHARINDEX('@',REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''))-1)
ELSE LEFT(REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''),2) END AS Col1,
CASE WHEN CHARINDEX('-',Val) >0
THEN SUBSTRING(REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''),CHARINDEX('-',REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''))+1,LEN(Val))
WHEN CHARINDEX('@',Val) >0
THEN SUBSTRING(REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''),CHARINDEX('@',REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''))+1,LEN(Val))
ELSE SUBSTRING(REPLACE(REPLACE(REPLACE(Val,'''',''),'(',''),')',''),3,LEN(Val))
END AS Col2
FROM @table tb
CROSS APPLY dbo.ParseValues(StrVal,',')t
)t

Input Values
----------------------------------
ID StrVal
1 ('AA1000','2008-20000' , '2009@20000')
2 ('BB2050','2009-44000' , '2008@20005')
3 ('2009@20000','AA1000','2008-20000')
4 ('CC2300','2008-12345' , '2009@43555')
5 ('ZZ5300','2008-21344' , '2009@23554')

output Values
------------------------------------------------------------
ID Col1 Col2
1 AA 1000
2 2008 20000
3 2009 20000
1 BB 2050
2 2009 44000
3 2008 20005
1 2009 20000
2 AA 1000
3 2008 20000
1 CC 2300
2 2008 12345
3 2009 43555
1 ZZ 5300
2 2008 21344
3 2009 23554

[/code]

boreddy
Posting Yak Master

172 Posts

Posted - 2008-11-07 : 00:49:12

hi visakh

here i am sending only one string only 'AA1000' for a record
i may have any special charector(not only -,@)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 00:56:53

OK. so they will all be in either of two formats? either alphabets followed by numbers or numbers seperated by special characters?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-07 : 00:59:25

Visakh, you forgot to post link for ParseValues

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 01:01:30

quote:
Originally posted by madhivanan

Visakh, you forgot to post link for ParseValues

Madhivanan

Failing to plan is Planning to fail

Yup..but no need as OP has specified he passes only single values.

boreddy
Posting Yak Master

172 Posts

Posted - 2008-11-07 : 01:22:17

yes exatly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 01:38:09

[code]DECLARE @Table table
(
ID int,
StrVal varchar(200)
)
INSERT INTO @Table
SELECT 1,'AA1000' union all
SELECT 2,'2008-20000' union all
SELECT 3,'2009@20000' union all
SELECT 4,'CC2300' union all
SELECT 5,'2008-12345'

SELECT * FROM @Table

SELECT ID,CASE WHEN PATINDEX('%[0-9]%',StrVal) >1
THEN LEFT(StrVal,PATINDEX('%[0-9]%',StrVal)-1)
WHEN PATINDEX('%[0-9]%',StrVal) =1
THEN LEFT(StrVal,PATINDEX('%[^0-9A-Za-z]%',StrVal)-1)
END AS Col1,
CASE WHEN PATINDEX('%[0-9]%',StrVal) >1
THEN SUBSTRING(StrVal,PATINDEX('%[0-9]%',StrVal),LEN(StrVal))
WHEN PATINDEX('%[0-9]%',StrVal) =1
THEN SUBSTRING(StrVal,PATINDEX('%[^0-9A-Za-z]%',StrVal)+1,LEN(StrVal))
END AS Col2
FROM @table tb

Input Values
-----------------------
ID StrVal
1 AA1000
2 2008-20000
3 2009@20000
4 CC2300
5 2008-12345

Output Values
--------------------------
ID Col1 Col2
1 AA 1000
2 2008 20000
3 2009 20000
4 CC 2300
5 2008 12345
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources