[SOLVED] Query postcode issue?

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

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

__________________
WelshStew Site Admin
If you think I've helped, click the "Thanks"
tierney rides tboard - uk site | xtreme wales - extreme clothing
WebForumz - facebook | LinkedIn

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.

__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder

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...

in oracle sql it would be:

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

it may be similar in mysql??

__________________
WelshStew Site Admin
If you think I've helped, click the "Thanks"
tierney rides tboard - uk site | xtreme wales - extreme clothing
WebForumz - facebook | LinkedIn

what if they don't put a space in?

__________________
WelshStew Site Admin
If you think I've helped, click the "Thanks"
tierney rides tboard - uk site | xtreme wales - extreme clothing
WebForumz - facebook | LinkedIn

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

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

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!!!

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