Wednesday, 26 November 2014
     
AUSQLUG Forums Home   Search   Login   Register   Member List  
SQL Server 2000 Forums  > SQL Server - Programming  > Script to split 1 col to 2  
 
Display using:  
Previous Thread :: Next Thread 
 Author Thread: Script to split 1 col to 2
sqlnonprog is not online. Last active: 14/05/2008 4:49:40 PM sqlnonprog
Joined: 11 Apr 2008
Total Posts: 1
 
Script to split 1 col to 2
Posted: 11 Apr 2008 09:24 AM
Hi All,

First post so my apologies if this has been discussed before. I need to update 2 columns in a table where one is a numeric the other is alpha from a single alphanumeric column that contains values such as:

12345A.
1234
1356B
156789
145767A

ie the result of the first example must be:

col 1 = 12345A
col 2 must = 12345
col 3 must = A

Any examples of a script that does this would be greatly appreciated. As you can probably tell I have not programmed in SQL Server very much!
philcart is not online. Last active: 7/01/2013 4:04:01 PM philcart
philcart.blogspot.com
Top 25 Poster
Forum Moderator
Joined: 20 Jul 2004
Total Posts: 345
 
Re: Script to split 1 col to 2
Posted: 17 Apr 2008 11:42 AM
Something like,

create table #tbl (col1 varchar(10))

insert into #tbl
select '12345A'
union all
select '1234'
union all
select '1356B'
union all
select '156789'
union all
select '145767A'

select
col1
, case when patindex('%[A-Z]%',col1) > 0 then left(col1, patindex('%[A-Z]%',col1)-1) else null end as col2
, case when patindex('%[A-Z]%',col1) > 0 then right(col1, len(col1)-(patindex('%[A-Z]%',col1)-1)) else null end as col3
from #tbl

drop table #tbl



Thanks
Phill

Colt 45 - the original point and click interface ;)
http://philcart.blogspot.com/
urid is not online. Last active: 12/04/2010 11:08:29 AM urid
Top 25 Poster
Joined: 18 Jun 2007
Total Posts: 34
 
Re: Script to split 1 col to 2
Posted: 29 Apr 2008 10:09 PM
Hi
Based on posted DDL

SELECT col1,SUBSTRING(col1,
PATINDEX('%[0-9]%',col1),
PATINDEX('%[^0-9.]%',SUBSTRING(col1,
PATINDEX('%[0-9]%',col1),20)+' ')-1),

SUBSTRING(col1,PATINDEX('%[A-Z]%',col1),
PATINDEX('%[^A-Z.]%',SUBSTRING(col1,
PATINDEX('%[A-Z]%',col1),20)+' ')-1)


FROM #tbl

Best Regards,
Uri Dimant
SQL Server MVP
http://dimantdatabasesolutions.blogspot.com/
Previous Thread :: Next Thread 
Page 1 of 1
 
AUSQLUG Forums  > SQL Server 2000 Forums  > SQL Server - Programming  > Script to split 1 col to 2