SQL - Splitting the string

Question

I am trying to split a string(eg. BUFORD GA 30518) based on spaces to fill into different fields City, State and zipcode. Please help me in writing Query for this in SQL

@alisha anjum Check and mark as correct if it works for you – Jibin Balachandran Dec 10 '15 at 05:48 — Jibin Balachandran, Dec 10 '15 at 05:48

Jibin Balachandran · Accepted Answer · 2015-12-15T07:23:03.037

0

Try this:

SELECT  City= LEFT(Column, CHARINDEX(' ',Column)-1)
,State= LEFT(SUBSTRING(Column, CHARINDEX(' ',Column)+1,LEN(Column)), CHARINDEX(' ',SUBSTRING(Column, CHARINDEX(' ',Column)+1,LEN(Column)))-1)
,zipcode= REVERSE(LEFT(REVERSE(Column), CHARINDEX(' ',REVERSE(Column))-1))

You can try this:

SELECT REGEXP_SUBSTR(t.column_one, '[^ ]+', 1, 1) col_one,
       REGEXP_SUBSTR(t.column_one, '[^ ]+', 1, 2) col_two,
       REGEXP_SUBSTR(t.column_one, '[^ ]+', 1, 3) col_three
FROM YOUR_TABLE t;

Can try this also:

SELECT SUBSTR(t.column_one, 1, INSTR(t.column_one, ' ')-1) AS col_one,
       SUBSTR(t.column_one, INSTR(t.column_one, ' ')+1) AS col_two,
       SUBSTR(t.column_one, INSTR(t.column_one, ' ')+1) AS col_three
FROM YOUR_TABLE t

edited Dec 15 '15 at 07:23

answered Dec 10 '15 at 05:43

Jibin Balachandran

3,381
1
24
38

Hi, can this be modified to be written in oracle as well? – alisha anjum Dec 15 '15 at 07:13
@alishaanjum I had updated the answer, try this. Mark it as useful if it works for you – Jibin Balachandran Dec 15 '15 at 07:24
Thanks alot. It is very useful. – alisha anjum Dec 15 '15 at 07:30
@alishaanjum Mark it as useful if it worked out for you – Jibin Balachandran Dec 15 '15 at 07:35
@alishaanjum I'm glad that it worked out for you – Jibin Balachandran Dec 15 '15 at 07:36

SQL - Splitting the string

1 Answers1