Using RTRIM to select first portion of a postcode 1

[JamesAlex]

Hello,

I need to write a query to return the first portion of a postcode, prior to the space, from an existing table. The problem I am having is that the required string can is of variable length - either 2, 3 or 4 characters. For example the postcodes may look as follows:

BU20 7DZ
P16 6GS
E1 2JL

I would want to return the following:

BU20
P16
E1

I have been attempting this using either RTRIM or SUBSTRING - fine for a fixed length string, but how should I adjust to cope with the variable lengths?

Any help would be much appreciated!

Thanks,

-James

 

[sunila7]

Try this query


select left(postalcode,patindex('% %',postalcode)) from tblname





Sunil

 

[JamesAlex]

Sunil,

many thanks for your swift response - your query does exactly what I need!

Cheers,

-James

 

[dbomrrsm]

Not a big problem I guess but just for reference the PATINDEX in the above is working out the position of the first space. In the case of BU20 7DZ this would be 5 so you get the left 5 of the postcode.

This will mean that all your data has a trailing blank.

To eliminate this (if you need to you can use

select left(postalcode,patindex('% %',postalcode)-1) from tblname

This will return 4 so you het just the charachters you want with no spaces after them

 

[JamesAlex]

Hi,

the first query has worked perfectly for my purpose, but it is always good to know how it works - thanks for your response.

-James