Getting last chars of 3 fields into one 1

[Danster]

Gday all,

I have 4 address fields in my customer table.
The last 4 characters in either addr2, addr3, or addr4 is the postcode.
I need to get a single postcode field
.

Sample data:

addr2 addr3 addr4
SuburbXX 1234
Level 12 14 Main St SuburbYY MA 3456
96 Main St SuburbYY NSW 2222

I've already tried
SELECT CASE ISNumeric (RIGHT(ADDR2,4) WHEN 1 THEN RIGHT(ADDR2,4) FROM Customers

That works OK for addr2, but I need to check each address field so I get a single postcode field. Ideas?

Cheers

Danster

 

[Ziggurat]

Hi Danster

One question that I would like to ask. Is the postcode always at the end ?

If it is, then concatenate all the fields together and the last 4 characters should be the post code.

Hope that this is the problem.



#
###
#####
#######
Ziggurat

 

[Danster]

Ziggurat,
That might just work! Postcode is usually at the end, but then the next addr field might have a one or two space entry there.

If someone could come up with another idea it'd be great, but in the meantime, Ziggs suggestion could be the easiest way.

cheers

 

[JamesLean]

You could try something like this:

SELECT CASE
  WHEN ISNUMERIC(RIGHT(addr2, 4)) = 1 THEN RIGHT(addr2, 4)
  WHEN ISNUMERIC(RIGHT(addr3, 4)) = 1 THEN RIGHT(addr3, 4)
  WHEN ISNUMERIC(RIGHT(addr4, 4)) = 1 THEN RIGHT(addr4, 4)
  END AS postcode
FROM table

If the fields might just have spaces in them after the postcode then you could try this also:

SELECT RIGHT(RTRIM(addr2 + addr3 + addr4), 4) AS postcode
FROM table

--James

 

[Danster]

James,

Nice! I reckon that'll do me.