SQL Server How to search all rows in a column for a value using STRING_SPLIT in an UPDATE Query

tf7tbtn2 于 11个月前发布在其他

关注(0)|答案(2)|浏览(74)

I have one table with a single number in each row and I need to determine if that number is in any row in another table that contains ; separated numbers.

I am currently using loops to go through the table searching the column for each row individually. As you can imagine, it is painfully slow.

I was hoping I could do something like: UPDATE [nbr_test] SET [found_flag] = 1 WHERE [nbr] IN ( SELECT [value] FROM STRING_SPLIT(( SELECT [numlist] FROM [findin] ),';') )

Surprisingly SSMS doesn't show any syntax errors, but I get the error "Subquery returned more than 1 value..." which makes sense, but is there a way to do something similar without using loops and taking forever? I am using SQL Server 2019

Here is an example:

CREATE TABLE [nbr_test] (
    [id]    int identity(1,1),
    [nbr]   varchar(50),
    [found_flag] bit
)

CREATE TABLE [findin] (
    [id]    int identity(1,1),
    [numlist] varchar(255)
)

INSERT INTO [nbr_test] VALUES
('12345',0),
('34567',0),
('56789',0)

INSERT INTO [findin] VALUES
('23456;67890'),
('98765;12345;01234'),
('06789'),
('56789')

And this is ideally what I'd want:

|id|nbr  |found_flag|
|1 |12345|1         |
|2 |34567|0         |
|3 |56789|1         |

sql-server

来源：https://stackoverflow.com/questions/77360189/how-to-search-all-rows-in-a-column-for-a-value-using-string-split-in-an-update-q

2条答案

按热度按时间

kd3sttzy1#

STRING_SPLIT() could only take an expression there (like a SELECT statement) if it produces a single result. As soon as there is more than one row in findin , this is no longer true. You can instead use CROSS APPLY to generate the IN() values:

UPDATE dbo.[nbr_test] 
  SET [found_flag] = 1 
  WHERE [nbr] IN 
  (
    SELECT s.[value] 
      FROM dbo.[findin] AS f
      CROSS APPLY 
      STRING_SPLIT(f.[numlist], ';') AS s
  );

Example db<>fiddle
Is it possible to also get the id from findin? Like SET found_flag=1, found_id=f.id ?

Sure, it gets a little more complicated. Here's one (possibly not the most efficient) way:

UPDATE n
   SET n.found_flag = 1,
       n.found_id   = sq.id
FROM dbo.[nbr_test] AS n
CROSS APPLY
  (
    SELECT f.id, s.[value] 
      FROM dbo.[findin] AS f
      CROSS APPLY 
      STRING_SPLIT(f.[numlist], ';') AS s
      WHERE s.[value] = n.nbr
  ) AS sq;

Updated db<>fiddle

赞(0）回复(0）举报 11个月前

iibxawm42#

You may simply be overcomplicating this. Why not just use LIKE?

UPDATE T
SET T.found_flag = 1
FROM
  @nbr_test T
INNER JOIN
  @findin F
ON
  -- The number is encapsulated in the list => <NoMatch>;<Match>;<NoMatch>
  F.numlist LIKE '%;' + T.nbr + ';%' 
  -- The number is at the end of the list and therefore has no trailing semicolon => <NoMatch>;<Match> 
  OR F.numlist LIKE '%;' + T.nbr 
  -- The number is at the beginning of the list => <Match>;<NoMatch>
  OR F.numlist LIKE T.nbr + ';%'
  -- The number is the entire list, so there are no semicolons
  OR F.numlist = T.nbr --<Match>

赞(0）回复(0）举报 11个月前

我来回答

SQL Server How to search all rows in a column for a value using STRING_SPLIT in an UPDATE Query

2条答案

相关问题

热门标签

最新问答