SELECT PART OF THE STRING 2

[cristi22]

Hello All -

Thanks for your help!

How do I extract the city & the state as separate fields?

Here is the data:

ADDRESS3

CAROLINA, PR
NEW YORK NY

It could be space or comma delimited.

 

[MarcLodge]

Hi Cristi,
Is the state always two letters, or can it be more?
Marc

 

[cristi22]

Hi Marc -

It's always two letters

Thank you
Cristi

 

[MarcLodge]

Cristi,
I think that you need to look at the RTRIM, LENGTH, LOCATE, and SUBSTR functions and have a play.

I knocked up the following in order to get the last two characters:

SELECT SUBSTR(ADDRESS3, LENGTH(RTRIM(ADDRESS3))-1)

which should give you the last two non blanks in the address line, but..... it didn't work for me! Even though the length of the rtrimmed field changed when I displayed it, it didn't seem to like it when executing as as soon as a shorter address line was rtrimmed it gave me an error. This may be the environment that I was working in, or a 'feature' of DB2, but if you get a similar problem you could get round it with a CASE statement working on the length of the rtrimmed address. I'm not a great fan of this solution as it's more than a little cumbersome, particularly if your address field if fairly large.

If the address does not start in position 1, you may have to use LTRIM too. In order to determine whether there is a comma at the end of the address, you may have to look into LOCATE of POSSTR and incorporate this in a CASE statement.

It's quite conceivable that I' have missed the obvious here, and I welcome any better ways to do it.

Hope this helps, get back to us if you get stuck.

Marc

 

[cristi22]

Your statement did now work, i could not figure out how to incorporate the LOCATE or POSSTR into SUBST
I've used
LTRIM (RIGHT ( ADDRESS_3,2)) & it worked

Still curious how to use the substring....

Thanks,
Cristi

 

[MarcLodge]

Well done Cristi, it's one of those things that probably has a few methods and answers. I think I got a bit blinkered when thinking this through, and LTRIM RIGHT is a great solution.

Glad to have helped you along the way, and thanks for the star.

Marc

 

[cristi22]

Thanks Marc!

Still curious about how to use SubStr....

 

[BrianTyler]

I would prefer to use:

right(ltrim(address),2)

This would allow for trailing spaces after the State.


Regards

Brian

 

[ddiamond]

Shouldn't it be rtrim?

right(rtrim(address),2)

 

[BrianTyler]

Oops, of course it should.

 

[cristi22]

Thanks all for your help!
What if I'd need to extract the partial string:

New York, Ny
New York Ny
New York Ny

How would I extract the city & the state?

Thanks,
cristi

 

[BrianTyler]

This should work

rtrim(left( rtrim('abc yz ') ,length(rtrim(address))-2))

Example:

db2 "values char(rtrim(left( rtrim('abc yz ') ,length(rtrim('abc yz '))-2))||'/'||right(rtrim('abc yz '),2)||'/')

 

[MarcLodge]

Cristi,
How about:

SELECT                                            
  SUBSTR(ADDRESS3,1,CASE                          
                    WHEN POSSTR(ADDRESS3,',') = 0   
                      THEN LENGTH(RTRIM(ADDRESS3))-2
                      ELSE POSSTR(ADDRESS3,',')-1   
                    END)                            
 ,RIGHT(RTRIM(ADDRESS3),2)

This should take care of when a comma appears. Problem would arise if a comma validly appeared more than once as the POSSTR would pick up the first comma.

The CASE statement can be applied to Brian's solution also.

Marc

 

[ddiamond]

The following might take care of the multiple comma problem:

CASE WHEN RIGHT(RTRIM(LEFT(ADDRESS3, LENGTH(ADDRESS3)-2))) = ","
  THEN LEFT(RTRIM(LEFT(ADDRESS3, LENGTH(ADDRESS3)-2)), LENGTH(RTRIM(LEFT(ADDRESS3, LENGTH(ADDRESS3)-2)))-1)
  ELSE RTRIM(LEFT(ADDRESS3, LENGTH(ADDRESS3)-2))
  END
 ,RIGHT(RTRIM(ADDRESS3),2)

If the left function gives you an error, you can try substr instead.