CASE statement headache

Question

CASE statement headache

faulknerwilliam2

SSCrazy

Points: 2299
More actions
August 20, 2019 at 11:29 am
Go to Answer
#3672702

Hello
I have an output in my queries that gives me:
xxxxxxx xxxxxxxxx : 123456 (xx) - xxxxxxx...
or
xxxxxxx xxxxxxxxx : 12345678 (xx) - xxxxxxx...
or
xxxxxxx xxxxxxxxx : TEST12345678 (xx) - xxxxxxx...
basically text before either a 6 or 8 digit number then text after.

Ideally I'd like to be able to CASE this column so I'd have an output where it's a 6 digit number = London and the output when it's an 8 digit number = Paris and the output when the number is prefaced by TEST as Test.

But I am very stuck on how to get the CASE statement to achieve this - essentially trim out a lot of text, work out if the number is either 6 or 8 digits long, then tell me if it's London or Paris. I'm not sure if it's possible.

Is this sort of CASE statement achievable? Advise / pointers would be very gratefully received. Thanks very much.
crow1969

Hall of Fame

Points: 3001
More actions
August 21, 2019 at 1:59 pm
Answer
#3673130
This can be done with a simple (ok, not quite simple) LIKE statement. In the below example, the [0-9] elements mean any digit. [^0-9] means any non-digit. Part of the trick is ordering or prioritizing the matches.
This will work for strings of 6 or 8 digits anywhere in the text. If you needed these from a particular point in the text, then it will need a bit more work.
```
-- Sample data setup
create table #test
(col1 int identity (1, 1) not null,
 col2 varchar(1000))

go
insert into #test (col2)
values ('hidehide123456hideho'),
('hodehodetest12345678hodeho'),
('hedehede12345678hedehe'),
('hidehidetest123456hideho')

-- Code example
select case when col2 like '%test%' then 'test'
when col2 like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%' then 'Paris'
when col2 like '%[0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%' then 'London'
else 'Uhh...somewhere' end
from #test
```
One other note. The above will treat a 7 digit string as 'London', as well. If you need to filter those out, you should put a [^0-9] to restrict the first digit.
- This reply was modified 5 years, 3 months ago by crow1969. Reason: Added a caveat for 7 digit numbers

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

I'm guessing you need to determine the length of the number and not the value? If so, is the xxx the pattern you can use or are you using this mean there can be other text in there?

What you want to do is some pattern matching with PATINDEX() or CHARINDEX() and then SUBSTRING to isolate out the number. From there, you can likely end up using LEN() to determine length.

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 2

Thanks Crow

Thanks crow1969; that worked very nicely and thanks for the tip re [^0-9]. Very helpful and much appreciated: thanks for taking the time and effort to get back to me.