|
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! |
|
|
 |
|
|
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
|
 |
| 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/
|
|
|
|