CODEWITHSUNDEEP
sqloracle-database
cableload
cableload

Reputation: 4375

sql query string based

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

Upvotes: 1

Views: 117

Answers (4)

João Silva
João Silva

Reputation: 91309

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.

Upvotes: 3

lc.
lc.

Reputation: 116478

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'

SQL Fiddle with this working

Upvotes: 1

Ben
Ben

Reputation: 52863

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.

Upvotes: 2

Yaroslav
Yaroslav

Reputation: 6534

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.

Upvotes: 2

Related Questions