T SQL string parsing - 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

SQL Server 2005 Forums

Transact-SQL (2005)

T SQL string parsing

Author

Topic

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-03-26 : 17:11:00

I have a table with a column Order_Request_ID (comes with a legacy system, grand fathered into SQL)

Order_Request_ID
439924_0004_PQR_0567
439924_0005_PQR_0567
439924_0006_PQR_0567
312902_0007_PQR
312902_0008_PQR
029329_0001_ASD
029329_0002_ASD
029329_0003_ASD
029329_0004_ASD
029329_0005_ASD

in case of 439924_0004_PQR_0567

439924 represents order_id
0005 represents part number
PQR represents department
and optional 0567 = promocode

what I want to do is parse string so that

in where clause

order_id = SUBSTRING('439924_0004_PQR_0567',1, CHARINDEX('_','439924_0004_PQR_0567')-1)
AND [part number] = '004' (I tried parsing but read below)
AND department = 'PQR'
AND IF exists promocode = '0567'

Once in a while data comes dirty (i.e. Order_Request_ID is like 012512_0125 which is missing department and throws T SQL errors.

Looking for an efficient approach. I really do not like non relational nature of Order_Request_ID

Thanks in advance...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 17:20:21

[code]DECLARE @Sample TABLE
(
OrderID VARCHAR(100)
)

INSERT @Sample
SELECT '439924_0004_PQR_0567' UNION ALL
SELECT '439924_0005_PQR_0567' UNION ALL
SELECT '439924_0006_PQR_0567' UNION ALL
SELECT '312902_0007_PQR' UNION ALL
SELECT '312902_0008_PQR' UNION ALL
SELECT '029329_0001_ASD' UNION ALL
SELECT '029329_0002_ASD' UNION ALL
SELECT '029329_0003_ASD' UNION ALL
SELECT '029329_0004_ASD' UNION ALL
SELECT '029329_0005_ASD'

-- Peso 1
SELECT SUBSTRING(OrderID, 1, 6) AS OrderID,
SUBSTRING(OrderID, 8, 4) AS PartNumber,
SUBSTRING(OrderID, 13, 3) AS Department,
NULLIF(SUBSTRING(OrderID, 17, 4), '') AS PromoCode
FROM @Sample

-- Peso 2
SELECT dbo.fnParseString(-1, '_', OrderID) AS OrderID,
dbo.fnParseString(-2, '_', OrderID) AS PartNumber,
dbo.fnParseString(-3, '_', OrderID) AS Department,
dbo.fnParseString(-4, '_', OrderID) AS PromoCode
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-03-26 : 19:25:51

Argh..! My head kept spinning... forget about your function with delimiter. I took the second approach as it may be more reliable.

Peso, thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources