Query on wild card chars - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query on wild card chars

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-30 : 05:07:00

Hi, i have a following data in my table. I want to give input as some name. the query has to retrive the rules which satisfy that name.

Suppose, if i gave name as "Guptasing", it has to retive rules like %gupt, %upt%, sing%

RNO Rules
1 gupt%
2 %pes
3 sa%
4 %upt%
5 %hob
6 %sin%
7 %gen%

input name is Guptasingh
Need output as below
Rules
gupt%
%sin%
%upt%

How can i write query for this?

developer :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 05:44:42

How you get the O/P like

Rules

%gupt
sin%
%upt%

The valid o/p is %upt%

select rules from Rule_master where 'guptasingh' like rules

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-30 : 05:48:12


Enclose the wildcards and the character string in single quotation marks, for example: 

LIKE 'Mc%' searches for all strings that begin with the letters Mc (McBadden).

LIKE '%inger' searches for all strings that end with the letters inger (Ringer, Stringer).

LIKE '%en%' searches for all strings that contain the letters en anywhere in the string (Bennet, Green, McBadden).

LIKE '_heryl' searches for all six-letter names ending with the letters heryl (Cheryl, Sheryl).

LIKE '[CK]ars[eo]n' searches for Carsen, Karsen, Carson, and Karson (Carson).

LIKE '[M-Z]inger' searches for all names ending with the letters inger that begin with any single letter from M through Z (Ringer).

LIKE 'M[^c]%' searches for all names beginning with the letter M that do not have the letter c as the second letter (MacFeather).

-------------------------
R..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-30 : 05:51:25

select rules from table
where @s like '%'+Rules+'%'

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 06:09:12

quote:
Originally posted by satish.gorijala

Hi, i have a following data in my table. I want to give input as some name. the query has to retrive the rules which satisfy that name.

Suppose, if i gave name as "Guptasing", it has to retive rules like %gupt, %upt%, sing%
RNO Rules
1 %gupt
2 %pes
3 sa%
4 %upt%
5 %hob
6 sin%
7 %gen%

input name is Guptasingh
Need output as below
Rules
%gupt
sin%
%upt%

in output how will u get the sin% when u give the input sing%?
How can i write query for this?

developer :)

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-30 : 06:10:51

Sorry, i gave wrong sameple data. when i give the name as input,what ever the rules can satisfy the name, that rules will come as output. For name "Guptasing", the rules %sin%, %upt% ,gupt% has to satisfy the input. I changed the data in table. I gave wrong sample data previously.

Table with data
RNO Rules
1 gupt%
2 %pes
3 sa%
4 %upt%
5 %hob
6 %sin%
7 %gen%

Output
Rules
gupt%
%sin%
%upt%

quote:
Originally posted by senthil_nagore

How you get the O/P like

Rules
gupt%
%sin%
%upt%

The valid o/p is %upt%

select rules from Rule_master where 'guptasingh' like rules

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

developer :)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 06:24:58

try this
declare @str varchar(max)

select @str = 'gupt%,%sin%,%upt%'

select * from tablename where patindex('%,'+Rules+',%', '%,'+@str +',%') > 0

select * from tablename where '%,'+@str+',%' like '%,'+Rules +',%'

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-07-30 : 06:56:48

declare @myData table
(
RNO int identity(1,1),
Rules varchar(100)
)
insert into @myData
select 'gupt%' union all
select '%pes' union all
select 'sa%' union all
select '%upt%' union all
select '%hob' union all
select '%sin%' union all
select '%gen%'

declare @Name varchar(50)
set @Name = 'gupta singh'

select * from @myData where @Name like Rules

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources