[SOLVED] Query postcode issue?

meth8200 · Feb 21st, 2008, 11:25

Hi Everyone,

Basically i've got a postcode query problem, i have a column with postcodes like:

abc 123
abcd 123
ab 123

What im trying to do is query only the first set on digits without the 123.
So my output should be:

abc
abcd
ab

Ive used the following technique:

SELECT TRIM(SUBSTRING(dbTest.postCode,1,4)) AS shortPostCode

FROM dbTest

This is not accurate as it chucks out:

abc
abcd
ab 1 <-?

Because im using a substring 1,4 ab 1 happens.

Any solutions please?? any suggestions would be helpful!

Thanks

welshstew · Feb 21st, 2008, 11:36

trim the last three, rather than selecting the first few (which is what I think you are trying to do)

in excel this would be something like:
=TRIM(LEFT(A1,LEN(A1)-3))

not sure about sql as not done it in about 4 years

Rob · Feb 21st, 2008, 12:14

Nice solution

Ideally, the seperate post code parts would reside in different database fields, but as they do not you need to work around it differently.

Either have SQL handle trimming off the last elements, or do it once the result-set has been returned.

You can with PHP, or ASP or whatever trim everything from the post after and including the space.

puzz · Feb 21st, 2008, 12:22

You can try something like this:

select substring(postcode,1,if(instr(postcode," ")=0,length(postcode),instr(postcode," "))) from my_table;

It will return part of your string from the first character to the first space...

welshstew · Feb 21st, 2008, 12:31

in oracle sql it would be:

select Rtrim(substr(postcode, 0, LENGTH (postcode) -3 ) ) from address

it may be similar in mysql??

welshstew · Feb 21st, 2008, 12:32

Quote:

Originally Posted by puzz

You can try something like this:

select substring(postcode,1,if(instr(postcode," ")=0,length(postcode),instr(postcode," "))) from my_table;

It will return part of your string from the first character to the first space...

what if they don't put a space in?

puzz · Feb 21st, 2008, 12:38

Quote:

Originally Posted by welshstew

what if they don't put a space in?

If he is sure that the right part is always 3 characters long then: trim(substring(postcode,1,length(postcode)-3));

meth8200 · Feb 21st, 2008, 12:54

thanks people, i've used puzz's solution although understanding -3 is also a benefit! cheers!

meth8200 · Feb 25th, 2008, 10:17

Hi People,

I know this is a mysql forum but would you know how this would work in sql enterprise

select substring(postcode,1,if(instr(postcode," ")=0,length(postcode),instr(postcode," "))) from my_table;

it doesnt like the if statement.......

works fine in mysql!

Cheers!!!

meth8200 · Feb 25th, 2008, 12:21

hi managed to sort it......no worries