Posted by Erland Sommarskog on 02/03/07 12:07

(alberto.estrada1@gmail.com) writes:
> Notice that I have everything displayed except for 'Test 200' and
> 'Test 300'. This have been replaced with the word 'Metals' and I'm
> only displaying it one time, no matter how often Test 200 shows up, or
> Test 300.
>
> My code looks like the following. It works good, except if I have to
> add 'Test 400' etc...I would have to hard code them and it will build
> up real quick. I looked at GROUPs to see if that would help, but I
> don't think it would because it has to be displayed with a different
> name such as 'Metals' for all the tests...Test 100, 200, etc... Is
> there a better way without having to add each number in it? I think
> the best way is to use a LIKE statment where the WHEN is being used
> but I keep getting errors if I use the LIKE sytnax where the WHEN is
> being used.

Without knowing the exact rules, it's difficult to give a whole-
covernig answer. But the condition

sample LIKE 'TEST [0-9][0-9]{0-9]'

could be used to handle all with Test plus a three-digit number.

SELECT sample
FROM mysamples
WHERE sample NOT LIKE 'TEST [0-9][0-9]{0-9]'
UNION ALL
SELECT DISTINCT 'Metals'
FROM MYSAMPLES
WHERE sammple LIKE LIKE 'TEST [0-9][0-9]{0-9]'

But I would suspect that your real business problem have a different
solution.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]