March 18, 2010 at 11:51 pm
CREATE TABLE mytbl2
(
c1 sysname
);
GO
INSERT mytbl2 VALUES ('Discount is 10-15% off');
INSERT mytbl2 VALUES ('Discount is .10-.15 off');
INSERT mytbl2 VALUES ('total count is 100 of total employees');
INSERT mytbl2 VALUES ('100 is total employees count');
INSERT mytbl2 VALUES ('100 is total employees count. here it is fine');
INSERT mytbl2 VALUES ('count of total employees is 100');
INSERT mytbl2 VALUES ('counting is 100');
INSERT mytbl2 VALUES ('total counting is 100');
GO
Declare @name varchar(100)
set @name = 'count'
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%[_]' + @name + ' %' or c1 LIKE '% ' + @name + '%' or c1 LIKE @name + ' %'
There results are
total count is 100 of total employees ------------------> correct
100 is total employees count --------------------------> correct
100 is total employees count. here it is fine -------------> correct
count of total employees is 100 ------------------------> correct
total counting is 100 -----------------------------------> this is incorrect
the last row should not appear.
Please help me searching the exact word given as parameter. it may
Shamshad Ali
March 19, 2010 at 1:10 am
This is interesting one.
March 19, 2010 at 1:23 am
I could think of simple option here is-
Declare @name varchar(100)
set @name = 'count'
SELECT c1
FROM mytbl2
WHERE
c1 LIKE '% '+@name
or
c1 LIKE @name+' %'
or
c1 LIKE '% '+@name+' %'
or
c1 LIKE '% '+@name+'.'+'%'
or
c1 LIKE '% '+@name+','+'%'
or
c1 LIKE '% '+@name+')'+'%'
Go on adding OR statements for each possible special characters u can think of (i.e. '.',',',')', etc)
March 19, 2010 at 1:58 am
Use a full-text index.
USE Test;
GO
CREATE TABLE dbo.Test
(
row_id INTEGER IDENTITY NOT NULL,
c1 NVARCHAR(128) NOT NULL,
CONSTRAINT [PK dbo.Test row_id]
PRIMARY KEY (row_id)
);
GO
INSERT dbo.Test (c1) VALUES ('Discount is 10-15% off');
INSERT dbo.Test (c1) VALUES ('Discount is .10-.15 off');
INSERT dbo.Test (c1) VALUES ('total count is 100 of total employees');
INSERT dbo.Test (c1) VALUES ('100 is total employees count');
INSERT dbo.Test (c1) VALUES ('100 is total employees count. here it is fine');
INSERT dbo.Test (c1) VALUES ('count of total employees is 100');
INSERT dbo.Test (c1) VALUES ('counting is 100');
INSERT dbo.Test (c1) VALUES ('total counting is 100');
GO
CREATE FULLTEXT CATALOG TestCatalog
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo;
CREATE FULLTEXT INDEX
ON dbo.Test (c1)
KEY INDEX [PK dbo.Test row_id]
ON TestCatalog
GO
DECLARE @word NVARCHAR(100);
SET @word = N'count';
SELECT T.row_id,
T.c1,
CT.[Rank]
FROM CONTAINSTABLE(dbo.Test, c1, @word, 10) CT
JOIN dbo.Test T
ON T.row_id = CT.[Key]
ORDER BY
CT.[Rank] DESC,
T.row_id ASC;
GO
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 2:56 am
That was interesting.
CREATE TABLE mytbl2
(
c1 sysname
);
GO
INSERT mytbl2 VALUES ('Discount is 10-15% off');
INSERT mytbl2 VALUES ('Discount is .10-.15 off');
INSERT mytbl2 VALUES ('total count is 100 of total employees');
INSERT mytbl2 VALUES ('100 is total employees count');
INSERT mytbl2 VALUES ('100 is total employees count. here it is fine');
INSERT mytbl2 VALUES ('count of total employees is 100');
INSERT mytbl2 VALUES ('counting is 100');
INSERT mytbl2 VALUES ('total counting is 100');
GO
Declare @name varchar(100)
set @name = 'count'
SELECT c1
FROM mytbl2
WHERE (c1 LIKE @name + ' %') or (c1 LIKE '% ' + @name + '%') and
(not (c1 like '%' + @name + '[a-z]%'))
drop table mytbl2
It works for this specific example, but I haven't tested it further. The sequence of the LIKE operators is important too.
BrainDonor
March 19, 2010 at 4:01 am
Very creative.
BrainDonor
It works for this specific example, but I haven't tested it further.
It is reasonable easy to find examples that break it. Nice though, as I say.
The sequence of the LIKE operators is important too.
It wouldn't if you use parentheses to make the logic explicit. As it stands, it depends on the precedence of OR versus AND.
Full-text search will be much more efficient for larger searches.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 4:07 am
Full-text search will be much more efficient for larger searches.
Definitely - I'd hate to wait for this code to complete against a large table. I just saw it as an interesting puzzle to solve as I've been playing with the LIKE command recently. Very easy to drag your code to a halt with it.
BrainDonor.
March 19, 2010 at 4:15 am
BrainDonor (3/19/2010)
I just saw it as an interesting puzzle to solve as I've been playing with the LIKE command recently.
It's even more fun to solve with a Regular Expression (available via CLR integration).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 4:20 am
Paul White NZ (3/19/2010)
BrainDonor (3/19/2010)
I just saw it as an interesting puzzle to solve as I've been playing with the LIKE command recently.
It's even more fun to solve with a Regular Expression (available via CLR integration).
You're one sick puppy...:-D
We have a developer here who doesn't understand how anyone can write code without an intimate knowledge of Regular Expressions.
We don't let him near customers.
March 19, 2010 at 4:23 am
BrainDonor (3/19/2010)
We don't let him near customers.
Very wise! :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 5:18 am
Excellent BrainDonor, really appreciable :Wow:
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 26, 2010 at 12:11 am
Hi there,
I just changed your where clause...
I hope this helps ^__^
Declare @name varchar(100)
set @name = 'count'
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%' + @name + '%'
AND c1 NOT LIKE '%[A-Z]' + @name + '%'
AND c1 NOT LIKE '%' + @name + '[A-Z]%'
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
March 26, 2010 at 12:15 am
btw guys, how do you know if someone replied to you in a forum or posted something in the same forum you posted? I mean do you have any notifications like facebook here or do you have to find that post again? hehehe!
🙂
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
March 26, 2010 at 12:19 am
how do you delete a post?
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
March 26, 2010 at 12:47 am
You should ask such questions in the forum of site
http://www.sqlservercentral.com/Forums/Forum433-1.aspx
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply