CASE WHEN LEFT(...) Pretty please - why isn't this working?

Question

CASE WHEN LEFT(...) Pretty please - why isn't this working?

Flamingo

Mr or Mrs. 500

Points: 551
More actions
April 4, 2012 at 7:25 am

#266866

The field RefCode has a total of 8 characters (varchar(8),null)- I need to flag this column on a variety of criteria.
This isn't working - any ideas why not? What I should I have?
CASE WHEN LEFT (RefCode,1) = 'C' AND LEFT (RefCode,2) BETWEEN '0' AND '9'
AND LEFT (RefCode,3) BETWEEN '0' AND '9' AND LEFT (RefCode,4) BETWEEN '0' AND '9'
AND LEFT (RefCode,5) BETWEEN '0' AND '9' AND LEFT (RefCode,6) BETWEEN '0' AND '9'
AND LEFT (RefCode,7) BETWEEN '0' AND '9' AND LEFT (RefCode,8) BETWEEN '0' AND '9' THEN '1'
WHEN LEFT (RefCode,1) = 'D' AND LEFT (RefCode,2) BETWEEN '0' AND '9'
AND LEFT (RefCode,3) BETWEEN '0' AND '9' AND LEFT (RefCode,4) BETWEEN '0' AND '9'
AND LEFT (RefCode,5) BETWEEN '0' AND '9' AND LEFT (RefCode,6) BETWEEN '0' AND '9'
AND LEFT (RefCode,7) BETWEEN '0' AND '9' AND LEFT (RefCode,8) BETWEEN '0' AND '9' THEN '1'
WHEN LEFT (RefCode,1) = 'C'AND LEFT (RefCode,2) = 'D'
AND LEFT (RefCode,3) BETWEEN '0' AND '9' AND LEFT (RefCode,4) BETWEEN '0' AND '9'
AND LEFT (RefCode,5) BETWEEN '0' AND '9' AND LEFT (RefCode,6) BETWEEN '0' AND '9'
AND LEFT (RefCode,7) BETWEEN '0' AND '9' AND LEFT (RefCode,8) BETWEEN '0' AND '9' THEN '1'
WHEN RefCode IN ('M9999998','H9999998','A9999998','C9999998','D9999998','CD999998','R9999981',
'X9999998') THEN '1'
WHEN LEFT (RefCode,1) = 'G' AND LEFT (RefCode,2) BETWEEN '0' AND '9'
AND LEFT (RefCode,3) BETWEEN '0' AND '9' AND LEFT (RefCode,4) BETWEEN '0' AND '9'
AND LEFT (RefCode,5) BETWEEN '0' AND '9' AND LEFT (RefCode,6) BETWEEN '0' AND '9'
AND LEFT (RefCode,7) BETWEEN '0' AND '9' AND LEFT (RefCode,8) BETWEEN '0' AND '9' THEN '1'
ELSE '0' END AS 'bgRefCode_Flag'

Viewing 8 posts - 1 through 7 (of 7 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

can you post sample data and the table DDL and an exlination of what you are trying to achieve. right now i see a case statement and have no context on where in the query its suposed to run and why its running. there may be a very good reason for it or we may be able to eliminate or simplify the case statement. but for either we need the DDL and sample data. please see the link in my signature for the way we like to see the data.

Help us help you.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Define 'not working'?

Throwing an error?

Doing something unexpected (if so what is it doing and what should it be doing)?

Converting the server to a black hole?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 3

Things like:

LEFT (RefCode,7)

will return all of the first 7 characters of the string. Is this actually what you want, or are you trying to select the 7th character only?

If it's the latter, then you need to use something like:

SUBSTRING(RefCode,7,1)

HowardW One Orange Chip Points: 29109 More actions · Answer 4

**Ignore this post, was meant for another thread and my session data in two tabs got mixed up**

Pam Brisjar SSChampion Points: 12094 More actions · Answer 5

LEFT() is going to get you everything from the start fo the string to the count you specify. So

DECLARE @STR VARCHAR(9)

SET @STR = '123456789'

SELECT LEFT(@str,3)

will return '123'

What you want is

SELECT SUBSTRING(@str,3,1)

to get the character in the third position.

However, for the code you have, I strongly suggest you look at PATINDEX. Here's a great article on it:

http://www.simple-talk.com/sql/t-sql-programming/patindex-workbench/?utm_source=simpletalk&utm_medium=email-main&utm_content=patindex-20110516&utm_campaign=SQL

Flamingo Mr or Mrs. 500 Points: 551 More actions · Answer 6

Flamingo

Mr or Mrs. 500

Points: 551

April 4, 2012 at 7:55 am

#1468871

All sorted now!

Thank you so much for your help

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I think this is what the OP is after:

select

case when refcode like 'C[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' then 1

when refcode like 'D[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' then 1

when refcode like 'CD[0-9][0-9][0-9][0-9][0-9][0-9]' then 1

when refcode like 'G[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' then 1

when refcode IN ('M9999998','H9999998','A9999998','C9999998','D9999998','CD999998','R9999981','X9999998') then 1

else '0'

end