I have a column which contains data like this
1 Virginia - VA,Maryland -MD,null
2 California - CA,Nebraska - NE,Minnesota - MN
3 Wyoming - WY,null,null
and so forth. Is there a way i can modify the column using string functions to look like this from plain sql?
1 VA,MD
2 CA,NE,MN
3 WY
Assuming there's nothing you can do with respect to your data, you can extract those values using REGEXP_REPLACE:
SELECT id, REPLACE(REGEXP_REPLACE(column_name, '[^-]+ - ?([A-Z]{2},?)', '\1'),
',null', '')
FROM your_table
Here's a working DEMO.
Yes it can be done. But there is a big but, SQL is a query language, is not meant to format strings or work with them. Better look to normalize your database as saving all that data in one column is a good indicator that you still have work to do on normalization of your tables. Better do this work in your application and not on the database using sql.
Yes, of course.
select substr(column_name, -2) from table_name
Though it would be better if you can get your data-provider to do it for you.
If the data is dirtier than indicated and your using Oracle 10g or later you could go down the regular expression route, but it doesn't seem to be required here.
select regexp_substr('California - CA', '[[:alpha:]]{2}$') from dual;
Here's a little SQL Fiddle to demonstrate.
Don't modify the column to look like you think you want it. Storing multiple values in a single column is Bad. Fix the table once and for all:
SELECT col1 AS ID, SUBSTR(SUBSTR(col2, 1, INSTR(col2, ',', 1, 1)-1), -2) AS STATE
FROM badTable
WHERE SUBSTR(SUBSTR(col2, 1, INSTR(col2, ',', 1, 1)-1), -4) <> 'null'
UNION ALL
SELECT col1 AS ID, SUBSTR(SUBSTR(col2, 1, INSTR(col2,',', 1, 2)-1), -2) AS STATE
FROM badTable
WHERE SUBSTR(SUBSTR(col2, 1, INSTR(col2,',', 1, 2)-1), -4) <> 'null'
UNION ALL
SELECT col1 AS ID, SUBSTR(col2, -2) AS STATE
FROM badTable
WHERE SUBSTR(col2, -4) <> 'null'
