Retrieving data from a postcode string

Question

Post reply

Retrieving data from a postcode string

James Knight

SSC Enthusiast

Points: 145
More actions
March 8, 2005 at 3:20 am

#163752

I need to retrieve the all the characters before the 1st numbers in a postcode string to determine the post town. examples of the data and required results are as follows:
WF16 0HN (I need to get WF back)
S41 7LG (I need to get S back)
I've looked through the text functions in the T-SQL reference but the main problem has been the fact that the length of the string doesn't determine the number of leading alpha characters. The only guarantee is that a 2 leading letter postcode will have a length of either 8 or 9 characters and a 1 leading letter postcode will have a length of either 7 or 8 characters.
Thanks for reading my babble!
James Knight

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Try this:

declare @inputstring as nvarchar(50) -- postcode input

declare @outputstring as nvarchar(50) -- postcode output

set @inputstring = 'WC1 2lf'

select @outputstring =

case when left(@inputstring,2) like '[a-z][a-z]%'

then left(@inputstring,2)

else

left(@inputstring,1)

end

select @outputstring

You can do this several ways. Basically, the [a-z] is a like operator that searches for any character between a-z

------------------------------
The Users are always right - when I'm not wrong!

James Knight SSC Enthusiast Points: 145 More actions · Answer 2

James Knight

SSC Enthusiast

Points: 145

March 8, 2005 at 6:17 am

#545849

Thanks for that. All works fine now.

David Burrows SSC Guru Points: 65142 More actions · Answer 3

A bit late, but....

SELECT LEFT(@inputstring,PATINDEX('%[0-9]%',@inputstring)-1)

Far away is close at hand in the images of elsewhere.
Anon.

Simon Martin SSC-Addicted Points: 468 More actions · Answer 4

On a similar note I was wondering how to split the 1st part of the postcode out i.e. if the postcode is 'se9 8jk' then I would want to return 'se9' but if the postcode were 'se18 9jk' I would want 'se18'

Sharing knowledge saves valuable time!
Simon Martin

David Burrows SSC Guru Points: 65142 More actions · Answer 5

SELECT LEFT(@postcode,CHARINDEX(' ',@postcode)-1)

Far away is close at hand in the images of elsewhere.
Anon.

Simon Martin SSC-Addicted Points: 468 More actions · Answer 6

Am I right in thinking that's splitting it at the space in the string?

So I need to enforce a space in the input (client side validation) and prevent postcodes without a space e.g. 'se188kj'

Sharing knowledge saves valuable time!
Simon Martin

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Yes, if there is no space then it makes it difficult to split. There is another thread on this forum that discusses this topic and I think it assumes that the inbound part is always 3 chars and therefore you can get the outbound by

SELECT LEFT(@postcode,LEN(@postcode)-3)

Far away is close at hand in the images of elsewhere.
Anon.

Simon Martin SSC-Addicted Points: 468 More actions · Answer 8

Superb. I'll hunt that one down

Sharing knowledge saves valuable time!
Simon Martin

James Knight SSC Enthusiast Points: 145 More actions · Answer 9

You can't work on the 1st 3 characters being the 1st part as some have 4! An example being WC1V.

You can always trust the post office to create a logical format!

Simon Martin SSC-Addicted Points: 468 More actions · Answer 10

Yeah - I had worked out a LEFT TRIM after 3 characters version - but then that was matching 'SE1' and 'SE18'

Sharing knowledge saves valuable time!
Simon Martin

James Knight SSC Enthusiast Points: 145 More actions · Answer 11

In the end my trigger has the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 2)) where left(addr_postcode,2) like '[a-z][a-z]%'

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 1)) where left(addr_postcode,2) not like '[a-z][a-z]%'

But from you code, I can simplify it (and make it more maliable) by the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = LEFT(addr_postcode,PATINDEX('%[0-9]%',addr_postcode)-1)

James Knight SSC Enthusiast Points: 145 More actions · Answer 12

In the end my trigger has the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 2)) where left(addr_postcode,2) like '[a-z][a-z]%'

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 1)) where left(addr_postcode,2) not like '[a-z][a-z]%'

But from you code, I can simplify it (and make it more maliable) by the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = LEFT(addr_postcode,PATINDEX('%[0-9]%',addr_postcode)-1)

James Knight SSC Enthusiast Points: 145 More actions · Answer 13

In the end my trigger has the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 2)) where left(addr_postcode,2) like '[a-z][a-z]%'

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = left(addr_postcode, 1)) where left(addr_postcode,2) not like '[a-z][a-z]%'

But from you code, I can simplify it (and make it more maliable) by the following:

UPDATE address

set addr_region = (select pcode_region from postcoderegions where pcode_prefix = LEFT(addr_postcode,PATINDEX('%[0-9]%',addr_postcode)-1)

Simon Martin SSC-Addicted Points: 468 More actions · Answer 14

Nice

I'll give that a whirl

Sharing knowledge saves valuable time!
Simon Martin