Searching Text fields!! 1

[faisal]

Can anybody tell me how can we seacrh in a text field using SELECT statement.<br>
<br>
Thanks<br>
-Faisal<br>
Faisal@visualsoft-inc.com

 

[DarkMan]

If you are searching for a particular searh term, you can use the % wilcard, like:<br>
<br>
SELECT textfield FROM table<br>
where textfield LIKE '%searchterm%'<br>
<br>
The % will return any string of zero or more characters, so this query will return all "textfield" columns that contain the word "searchterm"...<br>
<br>
Hope this helps.<br>
<br>
Doug

 

[faisal]

Thanks for the response it really help me!<br>
<br>
One thing more(if you dont mind). <br>
By default the search is case insensitive I suppose(I've test it).<br>
Can we make it case sensitive?<br>
<br>
-Faisal

 

[DarkMan]

I know that there is an option whether or not to make sort orders and the like case sensitive when you first install SQL Server, but I haven't been able to figure out how to make a select statement case sensitive. I've tried this before and was not able to make SQL Server return values that matched my search criteria by case.<br>
<br>
What I did was check my results in the application environment (Visual Basic) where I was able to test for case and then output the results...<br>
<br>
Is there anyone else who has come across this? And, if so, how did you deal with it?<br>
<br>
Doug

 

[nilsjh]

Hi, what about UCASE() or UPPER() (i can't remember the name). If you search for<br>
<br>
SELECT * FROM Employees WHERE UCASE(Name) = '%MICHAEL%'<br>
<br>
(Remember that using a % before the search text slows down the search)<br>
<br>
<br>

 

[jnicho02]

Fine, but that would be making a case-sensitive installation insensitive wouldn't it, as it doesn't mind if 'Name' is upper or lower case.<br>
<br>
Maybe some of the new text search features could do it. Otherwise, the only way (in 6.5 at least) would be to change the sort order - which is a nasty operation.

 

[calahans]

Hi guys,<br>
<br>
You select an option when you install SQL Server, as to whether the installation will be case sensitive or not. Once you have finished your installation, and wish to change from case insensitive to sensitive or vice versa you must rebuild your databases and reload your data.<br>
<br>
C

 

[MapMan]

The correct function is Upper() and it would only work if you wanted to compare All Upper Case to All Upper Case. But if you wanted to treat "Michael" different than "michael" or "MICHAEL" that won't help.<br>
<br>
I don't have the exact anyswer. But, Upper(), or Lower() for that matter, will not help in all cases.<br>
<br>
HTH,<br>
<br>
MapMap