Where clause

[gio2888]

Hi, can you put a wild card after the where clause?


select * from portal where (anyfield) = 'sometext'

Thanks in advance

 

[SQLSister]

select * from portal where (anyfield) = 'sometext%'
if you want records starting withthe search string.


select * from portal where (anyfield) = '%sometext%'
If you want records where the search string is in the middle of the field. You might note that this form of serach is very inefficeint as SQL server canot use indexes with a wildcard as the first character. If you need to do this al ot or with a large dataset you should investigate if using full-text search is a better idea for you.

 

[SQLBill]

SQLSister has the right idea, yes it is possible to use a wildcard in a WHERE. BUT..you have to use LIKE.


WHERE (anyfield) LIKE '%sometext'

-SQLBill

 

[JayKusch]

OR ... are you asking that the "sometext" be found in any or all fields in the table itself?

Thanks

J. Kusch

 

[maswien]

I don't think it's possible to put a wild character right after where clause, I guess what you want to do is following SQL:


declare @cname as varchar(256)
declare @tid as int
declare @sql as varchar(1024)
select @tid=id from sysobjects where xtype='u' and name='table7'

DECLARE wildcursor CURSOR FOR
SELECT name from syscolumns where id = @tid and xusertype = '167'

OPEN wildcursor
FETCH NEXT FROM wildcursor into @cname
if (@@FETCH_STATUS = 0)
set @sql = 'select * from table7 where ' + @cname + ' like ''%John%'''

FETCH NEXT FROM wildcursor into @cname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = @sql + ' union all ' + 'select * from table7 where ' + @cname + ' like ''%John%'''

FETCH NEXT FROM wildcursor into @cname
END

CLOSE wildcursor
DEALLOCATE wildcursor
print @sql
exec(@sql)

 

[SQLSister]

Sorry I meant like. Good catch Bill. Guess that's what happens when you are in a hurry.

Mija, yes, you can use like and wildcard in a where clause. Your solution is very poor. Never use a cursor unless you have no other choice. For simple query like this to use a cursor is very bad programming. Cursors are extremely inefficent and should be avoided at all costs.

In my office programmers are forbidden to write cursors. On the very rare occasions when they have been necessary only the dba could write them. In two years, we've written maybe one cursor that wasn't for database admin purposes. They are simply not necessary 99% of the time they are used.

You should forget the cursor syntax and learn how to write set-based SQL.

 

[gio2888]

Thanks for all the ideas guys, I think what JayKusch is what I really need....I am trying to match 'sometext' in many different fields. So basically I am not checking it by 1 field column, instead I want to check is through all columns but without having to write 10 sql statements....

select * from portal where field1 = 'sometext'
select * from portal where field2 = 'sometext'
select * from portal where field3 = 'sometext'
select * from portal where field4 = 'sometext'
select * from portal where field5 = 'sometext'
select * from portal where field6 = 'sometext'
.
.
.

consolidating to 1 statement

select * from portal where (all fields) = 'sometext'

 

[gio2888]

Thanks for all the ideas guys, I think what JayKusch said is what I really need....I am trying to match 'sometext' in many different fields. So basically I am not checking it by 1 field column, instead I want to check is through all columns but without having to write 10 sql statements....

select * from portal where field1 = 'sometext'
select * from portal where field2 = 'sometext'
select * from portal where field3 = 'sometext'
select * from portal where field4 = 'sometext'
select * from portal where field5 = 'sometext'
select * from portal where field6 = 'sometext'
.
.
.

consolidating to 1 statement

select * from portal where (all fields) = 'sometext'

 

[maswien]

please see my last reply carefully, it does what you want.