Find in string between characters

[RJL1]

Hi again,

I have the need to extract 2 pieces of information from a string

I have the following string

803-0052-P-11-BR

I need to get the 11 as the size and
I need to get the BR as the color

The strin can vary in lengt startign with the size so it could be

803-0052-P-8-T or 803-0052-P-11-BR

I need a way to get what is in between the 3rd and 4th dash as the size and what is after the 4tgh dash as the color regardless of lenght of each of thos elements.

Any suggestions?

This is what I have so far

SELECT
I.ITEM,
I.DESCRIPTION,
LEFT(I.DESCRIPTION,1) AS 'GENDER'
FROM
ITEM I WITH(NOLOCK)
WHERE
I.COMPANY = '073'

Thanks in advance

 

[bborissov]

DECLARE @Test table (Fld1 varchar(200))
INSERT INTO @TEst VALUES ('803-0052-P-11-BR')
INSERT INTO @TEst VALUES ('803-0052-P-8-T')
INSERT INTO @TEst VALUES ('803-0052-P-11-BR')

SELECT REVERSE(LEFT(Fld1,CHARINDEX('-', Fld1)-1)) AS Color,
       REVERSE(SUBSTRING(Fld1,CHARINDEX('-', Fld1)+1,
                              CHARINDEX('-', Fld1,CHARINDEX('-',Fld1)+1)-
                                                  CHARINDEX('-', Fld1)-1)) AS Tsts
FROM (SELECT REVERSE(Fld1) AS Fld1 FROM @tEST) Test

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[djj55]

Try CHARINDEX AND SUBSTRING. Sorry I do not have time at the moment to go into this more.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

What Borissov said.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[bborissov]

I didn't said anything

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.