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.
August 20, 2019 at 2:06 pm
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.
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.
August 22, 2019 at 2:59 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply