sql query with substring charindex

Question

table1
::::::::::::::::::::::::::::::::::
id | id_data | id_t | value 
1  | 43      | 1    | 
2  | 46      | 1    | 111,112,113
3  | 43      | 2    | 
4  | 46      | 2    | 90,5

table2
:::::::::::::::::::::::::::::::::::
id_value | cat
112      | cat1
5        | cat2

Hi, I need some help here if possible, please.

I need to update table1.value where id_data is 43 with table2.cat where id_value = the digits after value's ',' 'till the next ',' if there is any for each group in 'id_t'

I tried with a simple query but it's returning some null but 'value' can't be null

update table1 
set value = (select cat from table2 
            where convert(nvarchar,id_value) = substring(value,5,3))
where id_data='43'

I've been trying to incorporate CHARINDEX to take from the ',' but i just can't figure it out how it works.

Ideally it should look like this:

::::::::::::::::::::::::::::::::::
id | id_data | id_t | value 
1  | 43      | 1    | cat1
2  | 46      | 1    | 111,112,113
3  | 43      | 2    | cat2
4  | 46      | 2    | 90,5

Can anyone point me on the right direction, please?

I guess it's simple.. but I'm still learning...

thanks in advance.

::::::::::::::::

UPDATE1

WITH UpdateableCTE AS
(
    SELECT t1.id
          ,t1.id_data
          ,t1.id_t
          ,SecondNr
          ,(
            SELECT t2.cat
            FROM @table2 AS t2 WHERE t2.id_value=SecondNr
           ) AS NewCat
           ,t1.value
    FROM @table1 AS t1
    OUTER APPLY(SELECT CAST('<x>' + REPLACE(x.value,',','</x><x>') + '</x>' AS XML).value('/x[2]','int')
                FROM @table1 AS x 
                WHERE x.id_t=t1.id_t AND x.value IS NOT NULL AND id_data='46') AS ID(SecondNr)
    WHERE t1.value IS NULL
)
UPDATE UpdateableCTE SET value=NewCat;
--somehow where id_data='43'

I'll leave here one table that looks a little more to the real one with all the id_data and fields:

17974492    1   999251  somevalue
17974493    2   999251  somevalue
17974494    3   999251  somevalue
17974495    4   999251  somevalue
17974496    5   999251  somevalue
17974497    43  999251  (thishsouldbeupdated)
17974498    6   999251  somevalue
17974499    7   999251  somevalue
17974500    46  999251  111,311
17974501    8   999251  somevalue
17974502    9   999251  somevalue
17974503    10  999251  somevalue
17974504    11  999251  somevalue
17974505    12  999251  somevalue
17974506    13  999251  somevalue
17974507    1   999252  somevalue
17974508    2   999252  somevalue
17974509    3   999252  somevalue
17974510    4   999252  somevalue
17974511    5   999252  somevalue
17974512    43  999252  (thisshouldbeupdated)
17974513    6   999252  somevalue
17974514    7   999252  somevalue
17974515    46  999252  98,98
17974516    8   999252  somevalue
17974517    9   999252  somevalue
17974518    10  999252  somevalue
17974519    11  999252  somevalue
17974520    12  999252  somevalue
17974521    13  999252  somevalue

General advice: _Don't_ store CSV data in your database tables. It means your data is not normalized and will usually be hard to work with. — Tim Biegeleisen, Jun 14 '17 at 15:44
Sorry, I do not get your logic... Is there `cat1`, because the following line with `id_data=46` has got the `112` somewhere between commas and you set `cat2`, because the following line as got a `5`? This is weird and smells awfully... — Shnugo, Jun 14 '17 at 15:56
`id_t` "groups" them on the first table, and each 'group' has an `id_data 46` record where the one id that should join for each `id_t` is(second record, after the ','). I need to update the `id_data 43` for each id_t — vvic, Jun 14 '17 at 16:25
@Shnugo Reading your comment again, I think I get what you mean now, sorry. Indeed, table1 and table2 join through the second record between commas on table1. I know it's kinda weird but i'm working over an already defined table which cannot be changed now. — vvic, Jun 14 '17 at 16:38
@vvic, that's bad... What would happen, if your first table has `111,112,5,113`? Would you take `cat`, `cat2` or both? Are there always exactly two rows per `id_t`? What is the purpose of `id_data`? Is the `id` column in strict order (will the id of the row with the CSV numbers always be id+1)? — Shnugo, Jun 14 '17 at 16:44
@Shnugo If it has `111,112,5,113` it should still take cat1 since the second part string(112) is the only one I need. That's why I thought I should be using `charindex` to extract the second part of the string. There's more rows per `idt`, that's just an example to simplify it but this current update should not touch the other ones anyway. `id_data` identifies which kind of data it's inside- ex: `43=category`, `46(from, to ,)= id of the category`, the final user is the one writing the category id on 46. Yes, the `id` is an autonumeric. Thanks for your time. — vvic, Jun 14 '17 at 16:55
@vvic Okay I think I understand now... [In this answer](https://stackoverflow.com/a/38274439/5089204) you'll find an easy and typesafe approach (take just the very first line!) to get a number out of a separated list. Try to modify my answer below to read the appropriate `cat` value. If you need help, just call... — Shnugo, Jun 14 '17 at 17:00

Shnugo · Accepted Answer · 2017-06-16T07:04:06.470

This is not clean, nor is it something I'd recommend, but you might find some help:

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (1,43,1,NULL)
,(2,46,1,'111,112,113')
,(3,43,2,NULL)
,(4,46,2,'90,5')

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (112,'cat1')
,(5,'cat2');

SELECT t1.id
      ,t1.id_data
      ,t1.id_t
      ,ID.List
      ,(
        SELECT t2.cat
        FROM @table2 AS t2 WHERE CHARINDEX(',' + CAST(t2.id_value AS VARCHAR(100)) + ',',',' + ID.List + ',')>0
       )
FROM @table1 AS t1
OUTER APPLY(SELECT x.value FROM @table1 AS x WHERE x.id_t=t1.id_t AND x.value IS NOT NULL) AS ID(List)
WHERE t1.value IS NULL

UPDATE: Your explanation about use the second number as update

Try this

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (1,43,1,NULL)
,(2,46,1,'111,112,113')
,(3,43,2,NULL)
,(4,46,2,'90,5')

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (112,'cat1')
,(5,'cat2');

WITH UpdateableCTE AS
(
    SELECT t1.id
          ,t1.id_data
          ,t1.id_t
          ,SecondNr
          ,(
            SELECT t2.cat
            FROM @table2 AS t2 WHERE t2.id_value=SecondNr
           ) AS NewCat
           ,t1.value
    FROM @table1 AS t1
    OUTER APPLY(SELECT CAST('<x>' + REPLACE(x.value,',','</x><x>') + '</x>' AS XML).value('/x[2]','int')
                FROM @table1 AS x 
                WHERE x.id_t=t1.id_t AND x.value IS NOT NULL) AS ID(SecondNr)
    WHERE t1.value IS NULL
)
UPDATE UpdateableCTE SET value=NewCat;

SELECT * FROM @table1

The concept is the updateable CTE, where you can use a normal SELECT to get the values you need. You then can update the derived table (as long as there are only columns of one single table affected) directly.

The OUTER APPLY uses a trick with XML to split the CSV list in order to read the second number.

UPDATE 2: Use your new sample data

The following will use the new sample data and work with the IDs:

DECLARE @table1 TABLE(id INT,id_data INT,id_t INT,value VARCHAR(100));
INSERT INTO @table1 VALUES 
 (17974492,1,999251,'somevalue')
,(17974493,2,999251,'somevalue')
,(17974494,3,999251,'somevalue')
,(17974495,4,999251,'somevalue')
,(17974496,5,999251,'somevalue')
,(17974497,43,999251,'(thishsouldbeupdated)')
,(17974498,6,999251,'somevalue')
,(17974499,7,999251,'somevalue')
,(17974500,46,999251,'111,311')
,(17974501,8,999251,'somevalue')
,(17974502,9,999251,'somevalue')
,(17974503,10,999251,'somevalue')
,(17974504,11,999251,'somevalue')
,(17974505,12,999251,'somevalue')
,(17974506,13,999251,'somevalue')
,(17974507,1,999252,'somevalue')
,(17974508,2,999252,'somevalue')
,(17974509,3,999252,'somevalue')
,(17974510,4,999252,'somevalue')
,(17974511,5,999252,'somevalue')
,(17974512,43,999252,'(thisshouldbeupdated)')
,(17974513,6,999252,'somevalue')
,(17974514,7,999252,'somevalue')
,(17974515,46,999252,'98,98')
,(17974516,8,999252,'somevalue')
,(17974517,9,999252,'somevalue')
,(17974518,10,999252,'somevalue')
,(17974519,11,999252,'somevalue')
,(17974520,12,999252,'somevalue')
,(17974521,13,999252,'somevalue');

DECLARE @table2 TABLE(id_value INT,cat VARCHAR(100));
INSERT INTO @table2 VALUES
 (311,'cat1')
,(98,'cat2');

WITH UpdateableCTE AS
(
    SELECT t1.id
          ,t1.id_data
          ,t1.id_t
          ,SecondNr
          ,(
            SELECT t2.cat
            FROM @table2 AS t2 WHERE t2.id_value=SecondNr
           ) AS NewCat
           ,t1.value
    FROM @table1 AS t1
    OUTER APPLY(SELECT CAST('<x>' + REPLACE(x.value,',','</x><x>') + '</x>' AS XML).value('/x[2]','int')
                FROM @table1 AS x 
                WHERE x.id_t=t1.id_t AND x.id_data=46) AS ID(SecondNr)
    WHERE t1.id_data=43
)
UPDATE UpdateableCTE SET value=NewCat;

SELECT * FROM @table1;

I think you lost me there...I'm sorry. So, the update query would be something like this? `update table1 set value = ( SELECT cat FROM table2 WHERE CHARINDEX(',' + CAST(id_value AS VARCHAR(100)) + ',',',' + ID.List + ',')>0 ) from table1 OUTER APPLY(SELECT x.value FROM table1 AS x WHERE x.Id_t=t1.Id_t AND x.value IS NOT NULL) AS ID(List) where Id_data='43'` — vvic, Jun 14 '17 at 21:04
I was testing it and it's almost there, the XML trick works very well. But if I execute as it is on your example it gets 0 rows to update. If I remove `WHERE t1.value IS NULL` it updates every row. So what if I want to update only `id_data='43'`? Also, how could I check the ',' only on `id_data='46'` and not where it's not null?(there are a lot more id_data apart from 43 and 46 which can contain data). I'll update the original question with what I tried. Thanks a lot for your help. — vvic, Jun 15 '17 at 21:12
I already had tried that but was getting an error due to some weird null, it's fixed and working perfectly now. Thanks a lot. — vvic, Jun 16 '17 at 09:50

sql query with substring charindex

1 Answers1

UPDATE: Your explanation about use the second number as update

UPDATE 2: Use your new sample data