Searching and selecting a recurring string from within a variable length field

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19243 More actions · Answer 1

PATINDEX will locate the start position of a specified pattern within an expression. Have a look at that (in BOL) as a starting point.

BrainDonor.

Steve Hall
Linkedin
Blog Site

Canazei65 SSCommitted Points: 1696 More actions · Answer 2

Thanks for the reply.

However, I don't actually want the position of the characters i.e. I actuallly want the output to be:

either

Grade

M0

M1

M2

etc

or

Grade

0

1

2

Reason being I need to compare the numeric values with those in a similar field to see which is highest (we can ignore null values for now).

Apologies if I've misunderstood.

Cheers

-Rich

Ken McKelvey SSCoach Points: 18484 More actions · Answer 3

Obviously you have to use the PATINDEX to do the SUBSTRING:

-- *** Test Data ***

CREATE TABLE #t

(

Grade varchar(8000) NULL

)

INSERT INTO #t

SELECT 'R1 - Background, M0 - No Maculopathy'

UNION ALL SELECT NULL

UNION ALL SELECT 'R0 - No Retinopathy, M0 - No Maculopathy'

UNION ALL SELECT 'R1 - Background, M2 - No Maculopathy'

UNION ALL SELECT 'R0 - No Retinopathy, M0 - No Maculopathy, P1 - Photocoagulation'

UNION ALL SELECT 'R0 - No Retinopathy, M0 - No Maculopathy'

UNION ALL SELECT 'R1 - Background, M1- No Maculopathy'

-- *** End Test Data ***

SELECT *

,SUBSTRING(Grade, PATINDEX('%M[0-2]%', Grade) + 1, 1) AS MGrade

FROM #T

Canazei65 SSCommitted Points: 1696 More actions · Answer 4

That works perfectly and will be very useful in future too.

Many thanks

-Rich