Need help understanding this statement

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 2000 Forums

SQL Server Development (2000)

Need help understanding this statement

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-07-18 : 10:51:55

Hi

Could someone shed some light on this SQL :


Select * from Products T where ' ' +  convert(Varchar(1000),   T.LongDescription ) + ' ' like '%[^a-z]' + Word + '[^a-z]%'

LongDescription has ntext data type

1 - Why does the statement have '' before and after the column name?
2 - Whats the difference between " like '%[^a-z]' + Word + '[^a-z]%' " and " like '%' + Word + '%' " ?
3 - Why do we include convert(Varchar(1000)..) in this statement? Does this mean that only the first 1000 charatcers are matched ?

Thanks for the info

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-18 : 11:23:02

1. --> May be the stuff inside ' are a part of Dynamic SQL
2. --> '%' ==> Starts with any alfa-numeric or any other character
'%[^a-z]' ==> Starts with any alfaabetic characters only
3. --> because ntext data type cannot be compared but varchar can be compared

Srinika

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-18 : 12:19:00

quote:
Originally posted by Srinika

1. --> May be the stuff inside ' are a part of Dynamic SQL
2. --> '%' ==> Starts with any alfa-numeric or any other character
'%[^a-z]' ==> Starts with any alfaabetic characters only
3. --> because ntext data type cannot be compared but varchar can be compared

Srinika

One change here...!

'%[^a-z]' = starting with any other character than alphabets a..z

Harsh Athalye
India.
"Nothing is Impossible"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-18 : 12:47:36

This looks like a job for full-text indexing...

http://www.sqlteam.com/item.asp?ItemID=114
http://www.databasejournal.com/features/mssql/article.php/3441981

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-07-19 : 05:27:28

Thanks for the answers. can I just confirm the following:

1 - convert(Varchar(1000), T.LongDescription ) : What does 1000 mean in this context? Does it mean that only the first 1000 characters are compared?

2 - Imagine I have this text 'database' in my LongDescription column. Then I run 2 different statements:

A - ....where ' ' + convert(Varchar(1000), T.LongDescription ) + ' ' like '%[^a-z]' + Word + '[^a-z]%'

B - ....where ' ' + convert(Varchar(1000), T.LongDescription ) + ' ' like '%' + Word + '%'

Now if I search for 'database':
--> Statement A finds the word
--> Statement B finds the word

Now I search for 'databas':
--> Statement A DOES NOT find the word
--> Statement B DOES find the word

So my question is: Why doesn't statement A work when searching for 'databas'.

Thanks
(I hope you understand what I am getting at)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-19 : 07:38:29

quote:
Does it mean that only the first 1000 characters are compared?

Yes.

quote:
Why doesn't statement A work when searching for 'databas'.

Because there is an 'e' after it, and 'e' is not like '[^a-z]%' (since it is like '[a-z]%').

As I say, you should consider using full-text indexing, which will do all the hard work for you. See the links I posted.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources