September 24, 2020 at 1:54 pm
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 + '%')
September 24, 2020 at 2:24 pm
What is your question?
September 24, 2020 at 2:39 pm
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
September 24, 2020 at 2:56 pm
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?
September 24, 2020 at 5:06 pm
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
September 24, 2020 at 5:47 pm
Hello
Yes, the user may enter two numbers and you want to search for both of them or might enter just one number
September 24, 2020 at 6:55 pm
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
September 24, 2020 at 7:52 pm
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
September 25, 2020 at 2:35 pm
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__
September 25, 2020 at 3:09 pm
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
Change is inevitable... Change for the better is not.
September 25, 2020 at 3:29 pm
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
September 30, 2020 at 4:49 pm
Heh... "The OP has left the building".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2020 at 11:03 am
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 + '%';
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy