March 8, 2005 at 3:20 am
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
March 8, 2005 at 5:18 am
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
March 8, 2005 at 6:17 am
Thanks for that. All works fine now.
March 8, 2005 at 6:29 am
A bit late, but....
SELECT LEFT(@inputstring,PATINDEX('%[0-9]%',@inputstring)-1)
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2005 at 4:54 am
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'
March 9, 2005 at 5:00 am
SELECT LEFT(@postcode,CHARINDEX(' ',@postcode)-1)
Far away is close at hand in the images of elsewhere.
Anon.
March 9, 2005 at 5:12 am
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'
March 9, 2005 at 5:28 am
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.
March 9, 2005 at 5:39 am
Superb. I'll hunt that one down
March 9, 2005 at 6:12 am
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!
March 9, 2005 at 6:17 am
Yeah - I had worked out a LEFT TRIM after 3 characters version - but then that was matching 'SE1' and 'SE18'
March 9, 2005 at 6:18 am
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)
March 9, 2005 at 6:18 am
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)
March 9, 2005 at 6:18 am
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)
March 9, 2005 at 6:21 am
Nice
I'll give that a whirl
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply