Post Code manipulation

[dtfrancis15]

I have the post codes for all our customers in the database, but I only want the letters at the start of each one - ie IP99 4HH will be IP, G1 4JJ will be G.

As you can see, some are two letters long, some are only one. How do I get only the letters from the postcode?

 

[JayKusch]

Afternoon,

this may not be eloquent ... but it will work:

DECLARE @code AS varchar(10)

DECLARE CodeCursor SCROLL CURSOR FOR
SELECT code
FROM TableName

OPEN CodeCursor

FETCH FIRST FROM codecursor
INTO @code

WHILE @@FETCH_STATUS = 0
BEGIN

IF substring(@code,2,1) not in('0','1','2','3','4','5','6','7','8','9')
PRINT 'CODE = ' + substring(@code,1,2)
ELSE
PRINT 'CODE = ' + substring(@code,1,1)

FETCH NEXT FROM CodeCursor INTO @code
END

CLOSE CodeCursor
DEALLOCATE CodeCursor

 

[fluteplr]

select
case when substring(code,2,1) between '0' and '9' then substring(code,1,1) else substring(code,1,2) end as newcode from mytable