if conditions in sql - 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)

if conditions in sql

Author

Topic

mariyana
Starting Member

6 Posts

Posted - 2008-08-11 : 06:32:53

I have 2 columns namely empname and department for the table employee. i will have to check to the database based on the follwing condition
1. * should be treated as wild character . i.e if the values is given as * alone then all the records should be fetched, a*b then starting with a ending with b, its like using * instead of % in both the columns

2. % in any column should be treated as just another symbol and so search based on % should not happen.

3. if just any character is given i.e 're' then it should perform substring match .

how to combine all these conditions for both columns while writing a stored procedure in an efficient way..

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 06:38:50

How about

DECLARE @searchString VARCHAR(50)

SET @searchString = '%a*b%'

SELECT * FROM yourTable
WHERE
yourcolumn LIKE '%' + REPLACE(REPLACE(@searchString, '%', '[%]'), '*', '%') + '%'

-------------
Charlie

mariyana
Starting Member

6 Posts

Posted - 2008-08-11 : 08:12:29

hi,

thanks for your reply....... but then i am stuck up with some other quiries here

if i give *abc then it should be treated as % abc and not as % %abc %, which is being done here. so the answer that i expect is character ending with abd but the output would be any value having abc continously in it.

is there any way where we get the variables in an array and loop through the following options to find where it falls and then execute the query accordingly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 08:24:38

didnt understand what you're asking. can you illustrate with some data?

mariyana
Starting Member

6 Posts

Posted - 2008-08-11 : 08:38:28

if suppose i give

scenario1:
empname = *a then the output should be employeenames ending with "a" and not output as Sagar. In the below
'%' + REPLACE(REPLACE(@searchString, '%', '[%]'), '*', '%') + '%'
it will produce Sagar also as output as "a" is one of the character in that.

scenario2: empanme = a* then the output shld be characters starting with a

scenario3: empname=a then it should display any empname with the "a" as one of the characters in it, not necessary abt the starting and ending.

Hope you got now wht i am coming to say..
Thanks,

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-11 : 09:43:23

[code]

DECLARE @searchString VARCHAR(50)

SET @searchString = '45'

SET @searchString = CASE
WHEN CHARINDEX('*', @searchString, 1) > 0 THEN REPLACE(REPLACE(@searchString, '%', '[%]'), '*', '%')
ELSE '%' + REPLACE(REPLACE(@searchString, '%', '[%]'), '*', '%') + '%'
END

SELECT * FROM yourTable
WHERE
yourcolumn LIKE @searchString
[/code]

-------------
Charlie

mariyana
Starting Member

6 Posts

Posted - 2008-08-11 : 20:51:20

Thanks charlie,

This is what i was looking into....... is there anyway where i can loop this for n number of columns or should i repeat this for each and every column....

i am new to sql server 2005 and in learning stage...

Thanks,

mariyana
Starting Member

6 Posts

Posted - 2008-08-12 : 00:30:41

is it possible to loop through using cursor, if so how????? can anybody post your comments on this with sample snipplets would help.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 00:40:00

quote:
Originally posted by mariyana

is it possible to loop through using cursor, if so how????? can anybody post your comments on this with sample snipplets would help.

Thanks,

why do you want use cursor? You could simply use while loop. b/w what are you trying to do inside the loop?

mariyana
Starting Member

6 Posts

Posted - 2008-08-12 : 01:29:51

SET @searchString = CASE
WHEN CHARINDEX('*', @searchString, 1) > 0 THEN REPLACE(REPLACE(@searchString, '%', '[%]'), '*', '%')
ELSE '%' + REPLACE(REPLACE(@searchString, '%', '[%]'), '*', '%') + '%'
END

I am trying to use the above for number of columns and in different tables. all belong to varchar data type.

Is it possible to create the above as function so that it can be called from anywhere..

if so how to call this function and from where.....

Thanks....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 03:55:04

yup. you can just do like this

CREATE FUNCTION GetSearchString
@searchString varchar(100)
RETURNS varchar(2000) 
AS
DECLARE @RetStr varchar(2000)
SET @RetStr = CASE
WHEN CHARINDEX('*', @searchString, 1) > 0 THEN REPLACE(REPLACE(@searchString, '%', '[%]'), '*', '%')
ELSE '%' + REPLACE(REPLACE(@searchString, '%', '[%]'), '*', '%') + '%'
END
RETURN @RetStr
GO


SELECT * FROm YourTable WHERE YourColumn LIKE dbo.GetSearchString(@SearchVal)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources