Friday, February 24, 2012

find a string within a string regardless of position within that string

I have a SQL query but need an extra column to output:
1234 if column D contains 'ABCD',
3456 if column D contains 'CDEF'
5678 if column D contains 'FGHI'
I have tried using CASE but I think that function needs an exact match
Can anyone help?
Thanks
Kay"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137450168.459219.295770@.f14g2000cwb.googlegroups.com...
>I have a SQL query but need an extra column to output:
> 1234 if column D contains 'ABCD',
> 3456 if column D contains 'CDEF'
> 5678 if column D contains 'FGHI'
> I have tried using CASE but I think that function needs an exact match
> Can anyone help?
> Thanks
> Kay
>
SELECT
CASE
WHEN d LIKE '%ABCD%' THEN 1234
WHEN d LIKE '%CDEF%' THEN 3456
WHEN d LIKE '%FGHI%' THEN 5678
END
FROM your_table ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks guys, my syntax was slightly muddled!
Appreciate the help
Rgds
Kay|||SELECT
D,
[output] = CASE
WHEN CHARINDEX('ABCD', D)>0 THEN '1234'
WHEN CHARINDEX('CDEF', D)>0 THEN '3456'
WHEN CHARINDEX('FGHI', D)>0 THEN '5678'
ELSE '--not found--'
END
FROM your_table_name;
"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137450168.459219.295770@.f14g2000cwb.googlegroups.com...
>I have a SQL query but need an extra column to output:
> 1234 if column D contains 'ABCD',
> 3456 if column D contains 'CDEF'
> 5678 if column D contains 'FGHI'
> I have tried using CASE but I think that function needs an exact match
> Can anyone help?
> Thanks
> Kay
>|||"KayC" <kay_chua@.yahoo.co.uk> wrote in message
news:1137452120.646322.28340@.g49g2000cwa.googlegroups.com...
> Thanks guys, my syntax was slightly muddled!
> Appreciate the help
> Rgds
> Kay
>
Perhaps you weren't muddled. There are two possible versions of the CASE
syntax. Maybe you were just thinking of the simple case version. The
following article has examples:
http://www.databasejournal.com/feat...cle.php/3288921
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

No comments:

Post a Comment