Advanced Concatenation

Last Post 07 Apr 2008 03:21 AM by SwePeso. 1 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

--
04 Apr 2008 10:59 AM
I have a string:

EXT_1-49LJF_CYWF_ITIL3point5-A-000000858- 0000002

I am trying to strip out the bolded A-000000858

The first part: 'EXT' will always be 'EXT' and will always be 3 characters and is followed by a '_'

The second part: '1-49LJF' has no consistency. The number of characters can differ before it is followed by a '_'

The third part: 'CYWF' will always be 4 characters but characters will change and is followed by a '_'

The forth part: A-000000858 is what i want to extract. It will always begin with it will begin with a character followed by a '-' followed by 9 characters, followed by a '-'.

Thank You,
Marc

SwePeso
New Member
New Member

--
07 Apr 2008 03:21 AM
-- Prepare sample data
DECLARE @Sample TABLE (ID INT IDENTITY(1, 1), Data VARCHAR(200))

INSERT @Sample
SELECT 'EXT_1-49LJF_CYWF_ITIL3point5-A-000000858-0000002' UNION ALL
SELECT CAST(NEWID() AS VARCHAR(200)) UNION ALL
SELECT 'EXT_1-17LJF_ABCD_ITIL30point51-F-000200104-00002'

-- Show the expected output
SELECT ID,
SUBSTRING(Data, 1 + NULLIF(PATINDEX('%-[A-Z]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-%', Data), 0), 11) AS Peso,
RIGHT(LEFT(Data, LEN(Data) - CHARINDEX('-', REVERSE(Data))), 11) AS Russell,
SUBSTRING(SUBSTRING(Data, CHARINDEX('_', Data, 5) + 6, LEN(Data) + 1 - CHARINDEX('_', Data, 5) + 6), PATINDEX('%-_-_________-%', SUBSTRING(Data, CHARINDEX('_', Data, 5) + 6, LEN(Data) + 1 - CHARINDEX('_', Data, 5) + 6)) + 1, 11) AS ProPete
FROM @Sample
You are not authorized to post a reply.

Acceptable Use Policy