SQL select from group of values

Question

I have a field containing continues groups of values separated with ~.

57|0|0|2|||~56|0|0|2|||~55|0|0|2|||~54|0|0|3|4|5|~53|0|0|4|||~52|0|0|4|||~51|0|0|2|||~

Each group starts with the an ID e.g. 54 following of 5 values separated with | e.g. |0|0|3|4|5| and ends with ~. How i can select the 3rd value from the end if i have an id e.g. ID=54 i want to select 3;

Any help will be much appreciated.

Answer: Not easily, not even in a database which supports full regex. You should _normalize_ your data, and get each of the five values onto separate rows. — Tim Biegeleisen, Oct 01 '18 at 08:52
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Oct 01 '18 at 08:52
`STRING_SPLIT` with `ROW_NUMBER`... but this function is available in SQL 2016 and no one knows if the splits are ordered. — Salman A, Oct 01 '18 at 09:22
storing data like this is indicative of a poor database design. Instead of trying to work round the problem you should structure your tables correctly. If you haven't studied database normalisation and entity relationships before, now would be a good time to do so. — ADyson, Oct 01 '18 at 09:22
Unfortunately normalizing the data is not an option.I'm not the database creator i'm just trying to create a report and I have to work with the field as it is. — Chris, Oct 01 '18 at 09:33
There was another question about splitting a String into multiple rows. [Under this link](https://stackoverflow.com/questions/21428612/splitting-the-string-in-sql-server) you will find a function in SQL Server which is supposed to do what I described. Nevertheless you should consider normalising your database. Codd did great job with those Normalforms! — Shuumi, Oct 01 '18 at 09:15

score 0 · Answer 1 · answered Oct 01 '18 at 09:47

You can use function from Shuumi to split string into rows, and code from other question to split separated rows into columns. Then you have data in table and you can do normal select:

;WITH Split_Names (ElementID,Element, xmlname)
AS
(
    SELECT ElementID,
    Element,
    CONVERT(XML,'<Names><name>'  
    + REPLACE(Element,'|', '</name><name>') + '</name></Names>') AS xmlname
      FROM [dbo].[func_Split] ('57|0|0|2|||~56|0|0|2|||~55|0|0|2|||~54|0|0|3|4|5|~53|0|0|4|||~52|0|0|4|||~51|0|0|2|||~','~') 
)

 SELECT Element,      
 xmlname.value('/Names[1]/name[1]','varchar(100)') AS ID,    
 xmlname.value('/Names[1]/name[2]','varchar(100)') AS Element1,    
 xmlname.value('/Names[1]/name[3]','varchar(100)') AS Element2,    
 xmlname.value('/Names[1]/name[4]','varchar(100)') AS Element3,    
 xmlname.value('/Names[1]/name[5]','varchar(100)') AS Element4,    
 xmlname.value('/Names[1]/name[6]','varchar(100)') AS Element5    
 INTO #tmp
 FROM Split_Names

 SELECT Element3 FROM #tmp WHERE ID = 54

t-clausen.dk · Answer 2 · 2018-10-01T11:02:23.440

With a data model like this, you will have very complex and slow queries. This will solve it, I strongly recommend reworking the model.

This will solve it:

DECLARE @t TABLE(x varchar(max))
INSERT @t 
VALUES
('57|0|0|2|||~56|0|0|2|||~55|0|0|2|||~54|0|0|3|4|5|~53|0|0|4|||~52|0|0|4|||~51|0|0|2|'),
('57|0|0|2|||~56|0|0|2|||~55|0|0|2|||~54|0|0|5|4|5|~53|0|0|4|||~52|0|0|4|||~51|0|0|2|')

DECLARE @pos INT = 3
DECLARE @element INT = 54

;WITH CTE as
(
  SELECT t.c.value('.', 'VARCHAR(2000)') substr
  FROM (
      SELECT x = CAST('<t>' + 
        REPLACE(REPLACE(x, '|', '/'), '~', '</t><t>') + '</t>' AS XML)
      FROM @t
) a
CROSS APPLY x.nodes('/t') t(c)
), CTE2 as
(
  SELECT substr
  FROM CTE
  WHERE 
    cast(concat('<x>',
      REPLACE(substr, '/', '</x><x>'
        ), '</x>') as xml).value('/x[1]','int') = @element
), CTE3 as
(
  SELECT
    t.c.value('.', 'int') val,
    row_number() over(partition by z order by 1/0) - 1 pos
  FROM (
      SELECT x = CAST('<t>' + 
          REPLACE(substr, '/', '</t><t>') + '</t>' AS XML),
          row_number() over(order by 1/0) z
      FROM CTE2
  ) a
  CROSS APPLY x.nodes('/t') t(c)
)
SELECT val
FROM CTE3
WHERE pos = @pos

SQL select from group of values

2 Answers2