CODEWITHSUNDEEP
sqloracle-database
Aleksander Dmitrijev
Aleksander Dmitrijev

Reputation: 11

Deleting values and splitting values from single column to multiple ones in SQL

I have a column named STREETNAME where I have data based on this model: townName, streetName, 1 where 1 is the number of a house of a certain streetName. How do I delete townName from the whole column and also put numbers of houses into a separate column named STREETNO?

Upvotes: 1

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest regexp_substr(). This seems like the simplest method in Oracle to split a string with components separated as commas:

select regexp_substr(streetname, '[^,]+', 1, 1) as town,
       regexp_substr(streetname, '[^,]+', 1, 2) as street,
       regexp_substr(streetname, '[^,]+', 1, 3) as num
from (select 'town, street, 123' as streetname from dual) x

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You could try using REGEXP_REPLACE here:

SELECT
    REGEXP_REPLACE(STREETNAME, '^[^,]+, (.*)$', '\1') AS STREETNAME_NO_TOWN,
    REGEXP_REPLACE(STREETNAME, '^.* (.*)$', '\1') AS STREETNO
FROM yourTable;

The above alias STREETNAME_NO_TOWN would only remove the town name (which is what your question asked for), thereby leaving the street and number. If instead you want to isolate only the street then use this:

REGEXP_REPLACE(STREETNAME, '^[^,]+, ([^,]+),.*$', '\1')

Upvotes: 0

Related Questions