How to use PIPES in T-SQL to bring two number based on user entry

Question

How to use PIPES in T-SQL to bring two number based on user entry

Ysa

Mr or Mrs. 500

Points: 553
More actions
September 24, 2020 at 1:54 pm

#3790683
Hello
I have a query that use wildcard to bring any value according to the user entry now the use want to enter two number at the same time so I was trying this with a "PIPE", please help
tb_phone.number IN ( '%' + @number + '%' | '%' + @number + '%')
Also I tried
tb_phone.number like ( '%' + @number + '%' | '%' + @number + '%')
- This topic was modified 4 years, 1 month ago by Ysa.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What is your question?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 2

How I can search two different numbers using wildcards.

I need to search for phone number user should be able to enter any number and bring the information which work with one phone number

t_phone.number like ( '%' + @Object + '%') this work, they can enter '305 or 3, 703.. and bring the information now they want to enter in the same field two different area codes such 703 | 305 so I was trying

t_phone.number like ( '%' + @Object + '%' | '%' + @Object + '%') and it didn't work due '%' data type

This reply was modified 4 years, 1 month ago by Ysa.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Are you saying that the column you are searching may contain two numbers (eg, '123|456') or that the user may enter two numbers and you want to search for both of them?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

Could each user entered number be assigned to a different variable? If you have number1 and number2 then something like this

select top(1) tp.number, tp.username, ...
from tb_phone tp
where tp.number like '%' + @number1 + '%' 
      or tp.number like '%' + @number2 + '%';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 5

Hello

Yes, the user may enter two numbers and you want to search for both of them or might enter just one number

Steve Collins SSCrazy Points: 2157 More actions · Answer 6

Here's some code to test for what works

declare
  @number1 varchar(12)='121',
  @number2 varchar(12)='126';

select top(1) tp.number, tp.username
from (values ('4581265', 'J'), ('14121587', 'K')) tp(number, username)
where tp.number like '%'+@number1+'%' or tp.number like '%'+@number2+'%';

Output

numberusername
4581265J

Then if one of the input numbers is null

declare
  @number1 varchar(12)=null,
  @number2 varchar(12)='126';

select top(1) tp.number, tp.username
from (values ('4581265', 'J'), ('14121587', 'K')) tp(number, username)
where tp.number like '%'+@number1+'%' or tp.number like '%'+@number2+'%';

Output (it still returns a number)

numberusername
4581265J

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

If the user can enter multiple items in the text box - is it up to the user to insert a pipe in the string? Or do you have some other method of generating the 'list' of items to be searched?

As for coding it in SQL Server:

Declare @inputParameter varchar(100) = '703|305';

Declare @testTable Table (id int Identity(1,1), testValue varchar(20));
 Insert Into @testTable (testValue)
 Values ('123456'), ('703123'), ('222333'), ('2230533');

 Select *
   From @testTable tt
  Cross Apply string_split(@inputParameter, '|') ss
  Where tt.testValue Like '%' + ss.value + '%';

Be aware that spaces will be included in the search - if the user enters '703| 305' then the matching will include the space. This also will not perform well for larger sets of data because an index cannot be utilized.

I would set it up for leading character searches only which could use an index.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 8

Thank you this work when the user enter the full number but the idea to wildcards is that they can type

@number1 + 56_4__

@number2 = 6__7__

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

Ysa wrote:

Thank you this work when the user enter the full number but the idea to wildcards is that they can type
@number1 + 56_4__
@number2 = 6__7__

Just like what I would ask if someone gave me such a requirement in real life, I have to ask before I'd spend any more than about 15 seconds of thought on this before I came up with the question of "Why on this good Green Earth would anyone have such a need and do you realize the sheer volume of telephone numbers that would be returned"?

With no reflection on you, this is a fairly ridiculous requirement and without some very strong, totally compelling, and accurate requirement to do such a thing other than the whimsical request of some user or program manager being "because we want it", I'd also ask them what color crack they smoking. 😀 What are they trying to do? Find out possible numbers that were written on a wet cocktail napkin? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

Ysa wrote:

Thank you this work when the user enter the full number but the idea to wildcards is that they can type
@number1 + 56_4__
@number2 = 6__7__

What does this mean?

What would be the expected results if they entered these 2 search strings?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

Heh... "The OP has left the building". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 12

Jeff Moden wrote:

Heh... "The OP has left the building". 😀

Too busy trying to cool down their server CPUs after all those wildcard searches, no doubt.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Chris Wooding SSCarpal Tunnel Points: 4555 More actions · Answer 13

If they've asked to be able to search for two area codes at a time, it won't be long before they ask to search for 3 or 4 or more. If you change the input parameter to a table type, you can search for as many as the front end allows. Borrowing Jeffrey Williams' test harness;

declare @InputParameter table (TestItem varchar(100));

insert @InputParameter (TestItem)
values  ('703'),('305');

declare @TestTable table (id int identity(1, 1), TestValue varchar(20));
insert into @TestTable (TestValue)
values ('123456'), ('703123'), ('222333'), ('2230533');

 select *
   from @TestTable tt
  cross apply @InputParameter ss
  where tt.TestValue like '%' + ss.TestItem + '%';