Searching a strin for a number

[TSHDave]

Hi,
I need to search a postcode field and select on the characters on the left handside of the first number.e.g. if the field was 'WA2 6AY' then i would only want 'WA' returned and if the field was 'W2 6AY' i would only want 'W' returned. Any ideas?

 

[skuhlman]

This would work if it's always 1 or 2 or... letters followed by a digit. I'm not sure it would be the best choice for totally unknown values with undetermined lengths...

SELECT (CASE
WHEN pc LIKE '[1234567890]%' THEN ''
WHEN pc LIKE '_[1234567890]%' THEN LEFT(pc,1)
WHEN pc LIKE '__[1234567890]%' THEN LEFT(pc,2)
...
ELSE pc END) AS mystring
FROM mytable

 

[TSHDave]

cheers skuhlman, however i also want to group and count the results but dont know what to group on as i cant group on TC_postcode.

my sql statement
SELECT (CASE
WHEN TC_postcode LIKE '[1234567890]%' THEN ''
WHEN TC_postcode LIKE '_[1234567890]%' THEN LEFT(TC_Postcode,1)
WHEN TC_postcode LIKE '__[1234567890]%' THEN LEFT(TC_Postcode,2)

ELSE TC_Postcode END) AS areacode, count(TC_POSTCODE)
FROM bgolbdb(nolock)
where udcalloutcome like '%registration%'
group by ????????

 

[ClaireHCM]

Try...

select areacode, count(areacode) from
(SELECT
CASE WHEN TC_postcode LIKE '[1234567890]%' THEN ''
WHEN TC_postcode LIKE '_[1234567890]%' THEN LEFT(TC_Postcode,1)
WHEN TC_postcode LIKE '__[1234567890]%' THEN LEFT(TC_Postcode,2)
ELSE TC_Postcode END AS areacode
FROM bgolbdb(nolock) where udcalloutcome like '%registration%') A
group by areacode

 

[TSHDave]

cheers
that worked a treat