Parsing a string

Last Post 21 Oct 2008 12:07 PM by SwePeso. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
GoodFella3993
New Member
New Member

--
21 Oct 2008 06:54 AM
I have a set of data:

1 N-12345
2 NL-12345
3 NLA-12345
4 NLABC-12345
5 NL12345


I need to remove the alpha characters and the hyphen if the first 1,2 or 3 leading characters are alpha characters followed by a hyphen.


results:

1 N-12345 ---> changes to 12345
2 NL-12345 ---> changes to 12345
3 NLA-12345 ---> changes to 12345
4 NLABC-12345 ---> not changed
5 NL12345 ---> not changed


Thank you,
Marc
GoodFella3993
New Member
New Member

--
21 Oct 2008 07:02 AM
I am actually struggling with how to return the values with out the leading alphas.

In other words, how do i get the value '12345' for 'N-123345', 'NL-12345', and 'NLA-12345'?

Thanks again,
Marc
nosepicker
New Member
New Member

--
21 Oct 2008 07:21 AM
Here is one way to do it (where "string" is your data that you want to parse):

SELECT
CASE WHEN LEN(LEFT(string, CHARINDEX('-', string))) <= 4 THEN SUBSTRING(string, CHARINDEX('-', string) +1, LEN(string)) ELSE string END

Edit: you may have to make a few changes to account for all the cases you may have, but this should at least give you a place to start
SwePeso
New Member
New Member

--
21 Oct 2008 12:07 PM
DECLARE @Sample TABLE
(
data VARCHAR(20)
)

INSERT @Sample
SELECT '-88888' UNION ALL
SELECT '99-12345' UNION ALL
SELECT 'N-12345' UNION ALL
SELECT 'NL-12345' UNION ALL
SELECT 'NLA-12345' UNION ALL
SELECT 'NLABC-12345' UNION ALL
SELECT 'NL12345'

SELECT data,
CASE
WHEN data LIKE '[a-z]-%' THEN SUBSTRING(data, 3, 20)
WHEN data LIKE '[a-z][a-z]-%' THEN SUBSTRING(data, 4, 20)
WHEN data LIKE '[a-z][a-z][a-z]-%' THEN SUBSTRING(data, 5, 20)
ELSE data
END AS Peso,
CASE
WHEN LEN(LEFT(data, CHARINDEX('-', data))) <= 4 THEN SUBSTRING(data, CHARINDEX('-', data) + 1, LEN(data))
ELSE data
END AS Nosepicker
FROM @Sample
GoodFella3993
New Member
New Member

--
22 Oct 2008 11:28 AM
Great work. Thank you for your help.
You are not authorized to post a reply.

Acceptable Use Policy