Need help on statement to split varchar column

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

Need help on statement to split varchar column

Author

Topic

electro321
Starting Member

2 Posts

Posted - 2011-12-05 : 13:46:09

I have a table that contains a part number column (varchar). A typical part number is something like this: XC520V-600FFGGI. I need to split the column into two - the first will contain the initial characters (prefix) and the second will contain what follows. So in the example above the prefix would be XC and the second column would be 520V-600FFGGI. I've seen some string-splitting statements but not one that would split only the initial characters. If anyone can help I would sure appreciate it. I do some db maintenance here at work but I'm no guru. Thanks in advance.

Don

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-05 : 13:59:51

Do you need to split them and insert as two separate columns in the same table?

Is the "first part" always the first two characters of the string?

electro321
Starting Member

2 Posts

Posted - 2011-12-05 : 14:45:50

The column that needs to be split is called "partno". I will have to create a new column "prefix" that will contain the first part that I remove from the partno column (i.e., the initial alpha characters).

No, the first part may be two or three, sometimes up to five characters in the string. It depends on the manufacturer's nomenclature (electronic components). Some are MC5555B42A, some are JANTX998745LX and so forth. I would need it to remove all the initial sequential alpha characters and place them in the prefix column I create. Hope that helps and thanks again.

Don

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-05 : 15:20:48

Not sure why you would want to do this. You can always just SELECT the part of the data that you want to for any comparison etc.

But here's a way.

Sample data

declare @t table (col varchar(100), firstpart varchar(50), secondpart varchar(100))
insert @t
select 'XC520V-600FFGGI', null,null
union all select 'VJC40C-600FFGGI', null,null
union all select 'KPNCV12-600FFGGI', null,null
union all select 'TESTNUM3-600FFGGI', null,null
union all select 'TESTNUMCHAR', null,null

Query
update t
set t.firstpart = 
case when PATINDEX ('%[0-9]%',col) > 0 
	then left(col,PATINDEX ('%[0-9]%',col) -1 ) 
	else col 
end
, t.secondpart = 
case when PATINDEX ('%[0-9]%',col) > 0 
	then right(col,len(col) - PATINDEX ('%[0-9]%',col) + 1)
	else ''
end
from @t t

Result

col                       firstpart                 secondpart
------------------------- ------------------------- -------------------------
XC520V-600FFGGI           XC                        520V-600FFGGI
VJC40C-600FFGGI           VJC                       40C-600FFGGI
KPNCV12-600FFGGI          KPNCV                     12-600FFGGI
TESTNUM3-600FFGGI         TESTNUM                   3-600FFGGI
TESTNUMCHAR                                         TESTNUMCHAR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 00:02:36

quote:
Originally posted by electro321

The column that needs to be split is called "partno". I will have to create a new column "prefix" that will contain the first part that I remove from the partno column (i.e., the initial alpha characters).

No, the first part may be two or three, sometimes up to five characters in the string. It depends on the manufacturer's nomenclature (electronic components). Some are MC5555B42A, some are JANTX998745LX and so forth. I would need it to remove all the initial sequential alpha characters and place them in the prefix column I create. Hope that helps and thanks again.

Don

do you mean this?


SELECT * INTO #T
FROM(
select 'MC5555B42A' as t union all
select 'JANTX998745LX' union all
select 'AVHFRDG'
)t

select LEFT(t,CASE WHEN PATINDEX('%[0-9]%',t)> 0 THEN PATINDEX('%[0-9]%',t) ELSE LEN(t)+1 END -1) as prefix,
STUFF(t,1,CASE WHEN PATINDEX('%[0-9]%',t)> 0 THEN PATINDEX('%[0-9]%',t) ELSE NULL END -1,'') AS suffix
FROM #T
DROP TABLE #T

output
----------------------------------
prefix	suffix
MC	5555B42A
JANTX	998745LX
AVHFRDG	NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources