How can I use a string from array to search for string

Question

I have a column in my SQL Server table (see example below). I need to write a query that will return the rows containing a given string, for instance 830. However, I need to search for any string within an string array.

As an example:

I have a string array like this '634,7,830'.
Call my query with '634,7,830' as input
Result should be row 3, 5, 6, and 7

This is as far as I am, but here I only get the last three rows and not the one containing '830'.

How can I get the missing one?

SELECT * 
FROM dbo.YYY
WHERE [DepartmentNo] IN (SELECT value 
                         FROM STRING_SPLIT('634,7,830', ','))

SQL Server doesn't support arrays data types; in fact (in my opinion) no *good* RDBMS should. A value in a RDBMS should represent an **atomic** value; that means it's it represents a single specific value. The *real* thing you need to do here is fix your design and normalise your data. — Thom A, Jul 04 '21 at 18:13
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jul 04 '21 at 20:08

John Cappelletti · Accepted Answer · 2021-07-04T18:55:54.123

1

You were close. Perhaps this will help

-- Just a Demonstrative Table Variable.

Declare @YourTable Table ([DepartmentNo] varchar(50))  Insert Into @YourTable Values 
 ('403,273,828')
,('403,273,829')
,('403,273,830')
,('403,273,831')
,('634')
,('634')
,('7')

-- The Actual Query
 
Select * 
 from @YourTable A
 cross apply string_split([DepartmentNo],',') B
 Where B.value in (select * from string_split('634,7,830',','))

Results

DepartmentNo    value
403,273,830     830
634             634
634             634
7               7

Here is a SVF to strip Control Characters

CREATE FUNCTION [dbo].[svf-Str-Strip-Control](@S varchar(max))
Returns varchar(max)
Begin
    Select @S=Replace(@S,char(n),' ')
     From  (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31) ) N(n)

    Return ltrim(rtrim(replace(replace(replace(@S,' ','†‡'),'‡†',''),'†‡',' ')))
End
--Select [dbo].[svf-Str-Strip-Control]('Michael        '+char(13)+char(10)+'LastName')  --Returns: Michael LastName

Then Updated Answer

Declare @YourTable Table ([DepartmentNo] varchar(50))  Insert Into @YourTable Values 
 ('403,273,828')
,('403,273,829')
,('403,273,830'+char(8))  -- Notice char(8)
,('403,273,831')
,('634')
,('634')
,('7')
 
Select * 
 from @YourTable A
 cross apply string_split([dbo].[svf-Str-Strip-Control]([DepartmentNo]),',') B
 Where B.value in (select * from string_split('634,7,830',','))

Results same as above or dbFiddle

edited Jul 04 '21 at 18:55

answered Jul 04 '21 at 18:25

John Cappelletti

79,615
7
44
66

Hi John. Does this work for you in SQL Server? I still only get 634;634;7 when I try your query – Birch78 Jul 04 '21 at 18:36
.. replacing “cross apply” with “where exists” would not return duplicates when two or more of the search values match the same department . eg search for(‘403,273‘) – lptr Jul 04 '21 at 18:39
@Birch78 Yes this works in SQL Server https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=925b9f2d8a41d2c4e434a48baaf517cb . Perhaps you have trailing control characters such as tabs or CRLFs – John Cappelletti Jul 04 '21 at 18:39
@lptr OP is not looking for distinct values. Clearly stated desired rows "3, 5, 6, and 7" – John Cappelletti Jul 04 '21 at 18:43
That is super strange. I did a copy paste of your code and replaced the @YourTable with the name of my table – Birch78 Jul 04 '21 at 18:44
@Birch78 I added a control character and notice it is now excluded from the results https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=f4dc33dc116bacf19c028b0b97228a77 – John Cappelletti Jul 04 '21 at 18:46
Take a peek at https://stackoverflow.com/questions/43148767/sql-server-remove-all-non-printable-ascii-characters/43149139#43149139 to strip control characters – John Cappelletti Jul 04 '21 at 18:47
Ahh now I see your point. In the query you provided is it possible to trim both my array and [DepartmentNo]? There might be white spaces – Birch78 Jul 04 '21 at 18:49
@Birch78 Just to be clear. Odds are you've got the stray tab or CLRF ... the function provided may be overkill, but it does eliminate the hunt-and-peck – John Cappelletti Jul 04 '21 at 18:57
Hi John. You are awesome, thanks a lot. I just need to figure out how to use functions in SQL but it seams it does what I need. – Birch78 Jul 04 '21 at 19:09
As far as I can see your code only trim tailing whitespace and not leading whitespace, right? – Birch78 Jul 04 '21 at 19:19
@Birch78 Just a random after thought. Before you try the function, you may want to remove spaces. ... cross apply string_split(replace([DepartmentNo],' ',''),',') B ... – John Cappelletti Jul 04 '21 at 19:19
The function removes leading and trailing control control characters, but you may only have extra spaces. Look at https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=c3a3cace170a59291515b286c214188f – John Cappelletti Jul 04 '21 at 19:23

How can I use a string from array to search for string

1 Answers1