Parsing/SubString function advice

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

Parsing/SubString function advice

Author

Topic

ajay_hk
Starting Member

4 Posts

Posted - 2008-08-08 : 20:26:08

Hi all...

I have two fields in the database, one is called sku and the other being productcode. sku is basically a concatination of 3 or more fields where the first part is a productcode. At present productcode is blank because of some source system bugs which are being rectified, but in the mean time, for some reports to be run i need the productcode field and decided to parse it off sku.

The problem i'm facing is i'm using the 'LEFT' function in the SQL. Some examples of what sku values look like -

HAS-A-CP12-R
WF2SSR-J-CP12-M
WS-B-CP12-R

The part that comes before '-' is the productcode (the part that is bolded in the above examples), so i tried using PATINDX to basically locate the '-' and then combine the expression with LEFT to get everything before '-' as the productcode, but the PATINDX part is bringing 0 for some reason... and unable to understand where i'm going wrong. Looked through the threads here to find a possible solution - but could not find one.

Gurus - any advice would be really helpful. Many thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-08 : 23:27:14

[code]DECLARE @TABLE TABLE
(
prod_sku varchar(20)
)
INSERT INTO @TABLE
SELECT 'HAS-A-CP12-R' UNION ALL
SELECT 'WF2SSR-J-CP12-M' UNION ALL
SELECT 'WS-B-CP12-R'

SELECT prod_sku,
product = CASE WHEN CHARINDEX('-', prod_sku) <> 0 THEN left(prod_sku, CHARINDEX('-', prod_sku) - 1) END,
sku = CASE WHEN CHARINDEX('-', prod_sku) <> 0 THEN right(prod_sku, LEN(prod_sku) - CHARINDEX('-', prod_sku)) END
FROM @TABLE
/*
prod_sku product sku
-------------------- -------------------- --------------------
HAS-A-CP12-R HAS A-CP12-R
WF2SSR-J-CP12-M WF2SSR J-CP12-M
WS-B-CP12-R WS B-CP12-R

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

ajay_hk
Starting Member

4 Posts

Posted - 2008-08-10 : 14:43:24

Thanks a lot @khtan... that worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources