Looking for a T-sql query that will convert UK postcodes

[eo]

I have played around with CharIndex, but cannot get this to work. I need to convert full UK postcodes from (example WD11 3RU to WD. I.e. drop any numeric or character to the right of the first numeric.

Any ideas?

EO
Hertfordshire, England

 

[eo]

And just to add, a postcode could have two, or one start alphas. So it could be WD11 3RU, or E1 2NY. So I need something to calculate when the first numeric from left to right occurs, and then to use RTrip to cut all the characters off from that point onwards.

Dunno how to est when the first (1, 2, 3, 4, 5, 6, 7, 8, 9 or 0) occurs when looking from left to right.

EO
Hertfordshire, England

 

[bborissov]

DECLARE @Tst varchar(800)
SET @Tst = 'WD11 3RU'

SELECT LEFT(@tst, CASE WHEN PATINDEX('%[0-9]%',@Tst) = 0
                            THEN 8000
                  ELSE PATINDEX('%[0-9]%',@Tst)-1 END)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.