Help Parsing String

[worldchanger]

I have a column of data, which looks like the following:

2143-2352-234
2143-D325-4
SC3-TLC-178
2412-2341C-382

Basically what I’m trying to accomplish is split this string up into three separate fields and separate them by the ‘-‘. Here is a sample of the code I’m trying to run to attempt this task. I can successfully get the first section split off, however the anything after that I can’t seem to get properly formatted.

Data = Database
List = Table Name
Serial = Column
Store = Database
T121 = Table Name

SELECT data.dbo.list.serial
SUBSTRING(data.dbo.list.serial, 0, charindex(‘-‘, data.dbo.list.serial)) as building
FROM data.dbo.list.serial, store.dbo.T121
WHERE data.dbo.list.serial = store.dbo.T121.serial

I had some code that I was using to get the center section there, but it would sometimes have the trailing ‘-‘ and sometimes it wouldn’t. My life would be simple if the strings I were parsing were fixed length, but the only thing they all have in common, is the fact it is ‘-‘ delimited.

 

[JayKusch]

Is it ALWAYS just 3 columns split by the "-"?

Thanks

J. Kusch

 

[Pattycake245]

If it is always 3 columns split by '-' with no spaces then:

SELECT data.dbo.list.serial
left(data.dbo.list.serial,charindex('-', data.dbo.list.serial)-1)
as building,
substring(ltrim(rtrim(data.dbo.list.serial)), charindex('-',data.dbo.list.serial)+1,
(len(ltrim(rtrim(data.dbo.list.serial))) - len(left(ltrim(rtrim(data.dbo.list.serial)),charindex('-',data.dbo.list.serial)))) -
charindex('-',reverse(ltrim(rtrim(data.dbo.list.serial))))) as whatever,
right(ltrim(rtrim(data.dbo.list.serial)),charindex('-',reverse(ltrim(rtrim(data.dbo.list.serial))))-1) as blah
FROM data.dbo.list.serial, store.dbo.T121
WHERE data.dbo.list.serial = store.dbo.T121.serial

Fill in you values accordingly

Tim