CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2substring
Tim Vavra
Tim Vavra

Reputation: 537

Find any letter in a string SQL Server 2008 R2

I have a string that contains a number and letter combination. It will look like this 1A , 10C, 15A etc. I have a case statement that applies a relative value to this number based on the number and letter combination. The statement looks like this:

When [Page] like '%[A-Z]' then left([Page],1)+((ASCII(convert(varchar,(RIGHT([Page],1))))-64)*.01)

This works great when the page number portion is less than 10 as in the case of 1A but when you look at 10C and 15A, they should be 10.02 and 15.01 respectively. Basically I want to use charindex to find [A-Z] in the left's length factor.

Has anyone done something like this?

Upvotes: 0

Views: 4213

Answers (2)

bvr
bvr

Reputation: 4826

Try this

When PATINDEX('%[A-Z]%', [Page]) > 0 
then left([Page],PATINDEX('%[A-Z]%', [Page])-1)+((ASCII(convert(varchar,(RIGHT([Page],1))))-64)*.01)

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

When [Page] like '[0-9][A-Z]' then left([Page],1)+((ASCII(convert(varchar,(RIGHT([Page],1))))-64)*.01) 
When [Page] like '[0-9][0-9][A-Z]' then left([Page],2)+((ASCII(convert(varchar,(RIGHT([Page],1))))-64)*.01) 
When [Page] like '[0-9][0-9][0-9][A-Z]' then left([Page],3)+((ASCII(convert(varchar,(RIGHT([Page],1))))-64)*.01)

etc

Upvotes: 2

Related Questions