create a Field for City and State that is in one field in DB2

So the city and state is in one field in the DB2
what would be the sybtex in SQL 2008 R2 to start on the far right
go back two positions (the State in two positions)
and create that as its own field?
jaymz69Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
try like this.
declare @table table (col varchar(100))
insert @table values ('SOMEWHERE       ST')
select col,
       RTRIM(REVERSE(SUBSTRING(REVERSE(col),3,LEN(col)))) FirstPart,
       REVERSE(LEFT(REVERSE(col),2)) SecondPart
  from @table
/*
col	FirstPart	SecondPart
SOMEWHERE       ST	SOMEWHERE	ST
*/

Open in new window

0
 
sshah254Commented:
select right(fieldname, 2) from ...
0
 
jaymz69Author Commented:
so that would take the state out and into its own field?

ex.  'SOMEWHERE       ST'

needs to be 'SOMEWHERE'     'ST'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.