How to use a PIPE to search in textbox parameter

Question

Hello

I have a parameter that allow the user to search for any phone number using 3 or for number.

so i have this in my query

t.phone.number like ( '%' + @number + '%' ) which work perfect

ow we need to search for two different numbers such area code 703, 305
so I was trying to do

t.phone.number like ( '%' + @Object + '%' | '%' + @Object + '%') and it didn't work I have data type issues with the '%"

How I can approach it

Answer 1

The LIKE predicate requires an expression or literal. The expression or literal must be the equivalent of a (single) search pattern. The pattern for T-SQL do not allow multiples patterns or match expressions like regex.

Thus you need seperate patterns.

E.g.

t.phone.number LIKE ( '%' + @number1+ '%' ) OR t.phone.number LIKE ( '%' + @number2+ '%' )

Or using string split:

SELECT *
FROM   phone
       CROSS APPLY STRING_SPLIT(@Numbers, ',') SS
WHERE  phone.number LIKE ( '%' + LTRIM(RTRIM(SS.value)) + '%' );

Answer 2

Good day @Ysa8989

Unfortunately like most people that come to the forum to get help you are too lazy to provide us with full information and try to focus on specific point assuming that this is the issue. Your request sound in first glance like a case study of an "XY problem"

t.phone.number like ( '%' + @number + '%' ) which work perfect

Is it?!?

Providing the right result and working perfect are totally two different things!

What I see here is a huge poetical for poor design and poor performance using explicit convert between types and/or using wrong data types and using an awful expensive filter will will probably lead to full scan of the table or index each time instead of using the power of index by using "index seek"

What I see here is a huge potential to sql injection attack!

I HIGHLY RECOMMEND YOU TO PROVIDE INFORMATION TO REPRODCUE THE SCENARIO, so we will have the tools to discuss the real question which might be re-design the query or the table.

please post:

1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).

t.phone.number like ( '%' + @Object + '%' | '%' + @Object + '%')

This code is not T-SQL. The use of "|" is taken from other languages probably. SQL Server uses "or" and in theory you can (BUT YOU SHOULD NOT!) use:

WHERE t.phone.number like ( '%' + @Object + '%') or t.phone.number like ( '%' + @Object + '%')   

If you need to use a single input of text which is numbers spitted by comma like "703, 305" then:

(1) you have another issue of poor performance and poor design since SQL Server does not work well with multiple values in one value! This mean that you first need to split these multiple values into SET of values

For better solution in this case you probably ( I cannot know without the missing information) use Table-Valued Parameter instead of simple VARCHAR Parameter (which was bad from the start since you speak about numbers and as mentioned above you use wrong data type!

(2) If you do want to continue this road (which is not recommended), then you can use the function STRING_SPLIT to split the input to values and then you can use simple use JOIN operation

It should probably look something like

Note! can be done with CTE

DROP TABLE IF EXISTS tbl56  
GO  

create table tbl56 (c int)  
go  
insert tbl56 (c) values (2),(6),(8),(0),(34)  
GO  

Declare @input varchar(100)  
SET @input = '6,3, 34'  
SELECT c   
from tbl56  
INNER JOIN (select CONVERT (INT , [value]) from string_split (@input, ',') ) T(a) ON T.a = tbl56.c  
GO  

I hope this help you, and if you have more question or need clarification then please provide the missing information

---

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin