-1

That's my table

123 | 123-456-345
234 | 332-433
221 | 221

That's the result that I want to get

123 | 123
123 | 456
123 | 345
234 | 332
234 | 433
221 | 221
RadHimself
  • 1
  • Can you please show us what you have tried so far? – Giorgos Betsos Sep 09 '16 at 08:18
  • 5
    what's your RDBMS? – BytesOfMetal Sep 09 '16 at 08:22
  • This question has been asked before: for [MySql](http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows), [SQL Server](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows), [Oracle](http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle), ... – trincot Sep 09 '16 at 08:32
  • 1
    Don't store multiple values in a single column. Fix your data model –  Sep 09 '16 at 08:35

4 Answers4

2

With SQL-Server you could do this:

DECLARE @tbl TABLE(ID INT,YourString VARCHAR(MAX));
INSERT INTO @tbl VALUES(123,'123-456-345'),(234,'332-433'),(221,'221');

SELECT tbl.ID
      ,x.value('.','int') AS Value
FROM @tbl AS tbl
CROSS APPLY(SELECT CAST('<x>' + REPLACE(YourString,'-','</x><x>') + '</x>' AS XML)) AS Casted(AsXml)
CROSS APPLY Casted.AsXml.nodes('/x') AS A(x)

The trick is, to transform your string 123-456-345 to valid XML with simple replacements of -: <x>123</x><x>456</x><x>345</x>.

This XML can be queried using .nodes(), to get all entries one after the other

Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

If you are using SQL Server 2016 , use STRING_SPLIT function.

SELECT ID,  value  
FROM Yourtable  
    CROSS APPLY STRING_SPLIT([YourColumn], '-');

For lower versions of SQL server,you can either follow what @shungo suggested,or you can create a user defined function to split string and call it in the select statement.

CREATE FUNCTION [dbo].[SplitWords]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

The write the script like below.

SELECT ID,Item
FROM Yourtable
CROSS APPLY [SplitWords] (YourColumn,'-')
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
0

If you are using Postgres you can use string_to_array() and unnest():

select t.id, x.val
from the_table t
    cross join lateral unnest(string_to_array(the_column), '-') as x(val);

But you should really fix your data model. Storing delimited strings in a single column is a really bad idea.

0

For the Oracle 11g here is a simple solution (Just replace tab with your-table-name and num, str with your-column-names):

WITH
tab1 AS (
SELECT num, SUBSTR(str,1,3) str FROM tab
UNION ALL 
SELECT num, SUBSTR(str,5,3) str FROM tab
UNION ALL 
SELECT num, SUBSTR(str,9,3) str FROM tab
)
SELECT * 
FROM tab1
WHERE str IS NOT NULL

It gives me result of:

1   123 123
2   123 345
3   123 456
4   221 221
5   234 332
6   234 433
massko
  • 589
  • 1
  • 7
  • 22