TSQL Functions
-
Does anybody know the code to use to search a string for the first space in the string and return anything to the left of the string?
I am trying to get the Postcode Area from a postcode. ie trying to get the L5 part of a postcode of L5 3QE
I don't believe there is a single function that can be used to achieve this and I believe I will have to combine a few functions together but I am not sure.
Thanks in advance
Carl
-
This is formated to work in a select statement... could be optimized to be used in a function.
Declare @Zip as varchar(6)
SET @Zip = 'L5 3QE'
Select LEFT(@Zip, CASE WHEN charindex(' ' , @Zip, 1) > 0 THEN charindex(' ' , @Zip, 1) ELSE 1 END - 1)
--L5
SET @Zip = 'L5_3QE'
Select LEFT(@Zip, CASE WHEN charindex(' ' , @Zip, 1) > 0 THEN charindex(' ' , @Zip, 1) ELSE 1 END - 1)
--no error
-
DECLARE @PostCode varchar(8)
SELECT @PostCode = 'NE1 1AA'
SELECT LEFT(@PostCode, CHARINDEX(' ', @PostCode) - 1)
-
Have you tried with 'NE11AA'?
-
Remi's is better for error trapping... so if you can possibly get postcodes with the space missing, use his.
-
Just now... it fails. I posted at the same time as you and hadn't considered there being no space.
-
You're idea is good, just need this tweak :
Select LEFT(PostCode, CHARINDEX(' ', PostCode) - 1) from dbo.YourTable where CHARINDEX(' ', PostCode) > 0
Now it works
.But I still don't know the context so this is not very constructive.
-
I know the feeling
. -
Guys,
I dug out a piece of code and messed about with it and it seems to do the trick although I don't fully understand what it does?
select client_id,
substring(post_code_insured,1,len(post_code_insured)-(charindex(' ', right(reverse(post_code_insured),len(post_code_insured))))),
from <client table>
Can you explain it?
Carl
-
Same thing, only more complicated
. -
The only thing I would say is your code brings back less rows than mine?
Does your code deal with NULLS?
-
It should do, by returning NULL. Are you using Remi's re-vamp of my code to do a Select from a table? If so, could the missing rows be rows with postcodes without spaces?
-
Oh... hang on... NULL > 0 evaluates to false so no, NULLs would be excluded by that Where clause.
-
if he's using my code, then yes.
That's what I said earlier, we can't provide the right anwser as long as we don't know the circumstances of use of the code.
-
Null > 0 = UNKNOWN (not true), but it doesn't = false.
Ya that actually makes sens somehow
.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply