3

I have a table with strings:

Orig_Value
-----------
Abc_abc_1.2.3
PQRST.abc_1
XY.143_z

I want to split this using _ and . with the desired output:

Original_Value    Col1    Col2   Col3   Col4   Col5
-------------     ----    ----   ----   ----   ----
Abc_abc_1.2.3     Abc      abc     1      2     3
PQRST.abc_1       PQRST    abc     1
XY.143_z          XY       143     z

Query for MSSql and MySQL or PostgreSQL will do.

I tried using substring function like below query, but its not applicable in all data I have:

code

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
fLen
  • 468
  • 4
  • 13
  • 25
  • 4
    You need to show what have you tried so far – Pரதீப் Feb 11 '16 at 03:39
  • Seems like bad schema design, where multiple fields are trying to coexist in the same column. – Joel Coehoorn Feb 11 '16 at 04:05
  • 2
    Also: posting images of code and data instead of the text for the code and data is considered very rude here. It makes it harder for us to help you. – Joel Coehoorn Feb 11 '16 at 04:06
  • Finally, do you know how many potential sections you could have? The SQL language really really likes to know how many columns and of what type will be in the results at query compile time. I can't stress that enough. Even a `Select *` is able to get that info from the table definition. If you can't get this in advance, you'll be stuck building dynamic sql. – Joel Coehoorn Feb 11 '16 at 04:09
  • +1 for bad design. however, if its crucial to split string you can call PostgreSQL's `regexp_split_to_array` `select regexp_split_to_array(data.col, '[\\W_:.]'::text) from (select 'Abc_abc_1.2.3' as col union select 'd_a_t_a' as col union select 'stu:pid' as col ) as data ` – Ilya Dyoshin Feb 11 '16 at 04:18
  • @JoelCoehoorn, I'm sorry if I posted an image here, I wasn't able to post it due to error occurred where in my text for the code is not format supported as like that. :( – fLen Feb 11 '16 at 05:30
  • 1
    Copy/paste the code into your post, highlight it, and use the code button on the toolbar. Stack Overflow will format it the way we want to see it. – Joel Coehoorn Feb 11 '16 at 06:25

3 Answers3

4

PostgreSQL

Following as an exampple,

CREATE TABLE foo (org_val TEXT);

INSERT INTO foo
VALUES ('Abc_abc_1.2.3')
      ,('PQRST.abc_1')
      ,('XY.143_z');

using regexp_split_to_array()

SELECT org_val
    ,a [1] col1
    ,a [2] col2
    ,a [3] col3
    ,a [4] col4
    ,a [5] col5
FROM (
    SELECT org_val
        ,regexp_split_to_array(replace(org_val, '.', '_'),'_') AS a
    FROM foo
    ) t

result:

org_val       col1  col2 col3 col4 col5 
------------- ----- ---- ---- ---- ---- 
Abc_abc_1.2.3 Abc   abc  1    2    3    
PQRST.abc_1   PQRST abc  1    NULL NULL 
XY.143_z      XY    143  z    NULL NULL

SQLFIDDLE-DEMO

Vivek S.
  • 19,945
  • 7
  • 68
  • 85
  • 1
    This answer required the least amount of effort for my case which was extracting parts of table names separated by underscores. It seems like `regexp_split_to_array` is also a good choice performance-wise. – Farzad Soltani Apr 18 '22 at 14:26
  • @FarzadSoltani Glad to hear that! – Vivek S. Apr 19 '22 at 08:18
2

Simple and fast in Postgres using basic functions translate() and split_part():

SELECT t.*
     , split_part(o, '.', 1) AS col1
     , split_part(o, '.', 2) AS col2
     , split_part(o, '.', 3) AS col3
     , split_part(o, '.', 4) AS col4
     , split_part(o, '.', 5) AS col5
FROM   tbl t, translate(t.orig_val, '_', '.') o;  -- implicit LATERAL join

Result exactly as desired.
SQL Fiddle.

Regular expression functions like advertised in another answer are powerful but expensive. This should be considerably faster. Compare:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

Here is one way to do it in SQL Server

;WITH cte
     AS (SELECT Replace(col, '_', '.') + '.' AS col
         FROM   (VALUES ('Abc_abc_1.2.3'),
                        ('PQRST.abc_1'),
                        ('XY.143_z')) tc (col))
SELECT original_col = col,
       column_1=COALESCE(LEFT(col, Charindex('.', col) - 1), ''),
       column_2=COALESCE(Substring(col, P1.POS + 1, P2.POS - P1.POS - 1), ''),
       column_3=COALESCE(Substring(col, P2.POS + 1, P3.POS - P2.POS - 1), ''),
       column_4=COALESCE(Substring(col, P3.POS + 1, P4.POS - P3.POS - 1), ''),
       column_4=COALESCE(Substring(col, P4.POS + 1, P5.POS - P4.POS - 1), '')
FROM   cte
       CROSS APPLY (VALUES (CASE
                     WHEN Charindex('.', col) >= 1 THEN Charindex('.', col)
                   END)) AS P1(POS)
       CROSS APPLY (VALUES (CASE
                     WHEN Charindex('.', col, P1.POS + 1) >= 1 THEN Charindex('.', col, P1.POS + 1)
                   END)) AS P2(POS)
       CROSS APPLY (VALUES (CASE
                     WHEN Charindex('.', col, P2.POS + 1) >= 1 THEN Charindex('.', col, P2.POS + 1)
                   END )) AS P3(POS)
       CROSS APPLY (VALUES (CASE
                     WHEN Charindex('.', col, P3.POS + 1) >= 1 THEN Charindex('.', col, P3.POS + 1)
                   END)) AS P4(POS)
       CROSS APPLY (VALUES (CASE
                     WHEN Charindex('.', col, P4.POS + 1) >= 1 THEN Charindex('.', col, P4.POS + 1)
                   END)) AS P5(POS)

Result:

╔════════════════╦══════════╦══════════╦══════════╦══════════╦══════════╗
║  original_col  ║ column_1 ║ column_2 ║ column_3 ║ column_4 ║ column_4 ║
╠════════════════╬══════════╬══════════╬══════════╬══════════╬══════════╣
║ Abc.abc.1.2.3. ║ Abc      ║ abc      ║ 1        ║        2 ║        3 ║
║ PQRST.abc.1.   ║ PQRST    ║ abc      ║ 1        ║          ║          ║
║ XY.143.z.      ║ XY       ║ 143      ║ z        ║          ║          ║
╚════════════════╩══════════╩══════════╩══════════╩══════════╩══════════╝
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172