CODEWITHSUNDEEP
sql-serversql-server-2008-r2
MAK
MAK

Reputation: 7260

SQL Server 2008 R2: Prepare dynamic where clause for given string

I have the following string to convert into the where clause.

Given string:

DECLARE @String VARCHAR(MAX) = 'Dateofjoining = '2015-01-01' AND Firstname = ''Sam'' AND Lastname = ''Mak'' AND PhoneNumber = 123'

Note: The given string used for condition check for the employee table where I am just getting an record of specific firstName and LastName of the employee. Now I want to add the SOUNDEX() function to the both ends of the given value in the string. So the expected WHERE clause should looks like as shown below.

Expected result:

SELECT * 
FROM dbo.Employee2
WHERE Dateofjoining = '2015-01-01' 
  AND SOUNDEX(Firstname) = SOUNDEX('Sam')  
  AND SOUNDEX(LastName) = SOUNDEX('Mak') 
  AND PhoneNumber = 123;

Upvotes: 0

Views: 51

Answers (2)

Abhishek
Abhishek

Reputation: 2490

The below query would do your work -

DECLARE @String VARCHAR(MAX) = 'Dateofjoining = ''2015-01-01'' AND Firstname = ''Sam'' AND Lastname = ''Mak'' AND PhoneNumber = 123'
DECLARE @Sql VARCHAR(MAX) = 'SELECT * 
FROM dbo.Employee2
WHERE '
SET @String = SUBSTRING(@String,1,PATINDEX('%AND%',@String)-1)+' AND '+
'SOUNDEX('+REPLACE(SUBSTRING(@String,PATINDEX('%AND%',@String)+3,dbo.Pos(@string,'''',1)+1),' = ',')= SOUNDEX(')+')'+' AND '+
'SOUNDEX('+REPLACE(SUBSTRING(@String,dbo.Pos(@string,'AND',2)+4,dbo.Pos(@String,'AND',3)-dbo.Pos(@String,'AND',2)-4),' = ',') = SOUNDEX(')+')'+' AND '+
SUBSTRING(@string,dbo.Pos(@String,'AND',3)+4,LEN(@String))
SELECT @Sql + @string

The function code to find the occurrence of ' -

CREATE FUNCTION dbo.Pos(@string VARCHAR(MAX),@delim VARCHAR(10),@occur INT)
RETURNS INT
AS
BEGIN
DECLARE @pos int, @counter int, @ret int
SET @pos = CHARINDEX(@delim, @string)
set @counter = 1
if @occur = 1 set @ret = @pos
else
begin
while (@counter < @occur)
begin
select @ret = CHARINDEX(@delim, @string, @pos + 1)
set @counter = @counter + 1
set @pos = @ret
end
end
RETURN @ret
END

Edit: Made changes as per the requirement.

Upvotes: 1

Krish KvR
Krish KvR

Reputation: 1054

Try something like this 

create table #temp(Num int,FName Varchar(50),SName varchar(50))
insert into #temp values(1,'Sam','Mark')
insert into #temp values(1,'Jan','Feb')

DECLARE @Con1 VARCHAR(MAX) 
DECLARE @Val1 VARCHAR(MAX) 
DECLARE @Con2 VARCHAR(MAX) 
DECLARE @Val2 VARCHAR(MAX) 
set @Con1  = 'FName' 
set @Val1  = 'Sam'
set @Con2  = 'SName' 
set @Val2  = 'Mark'

DECLARE @str VARCHAR(MAX)
SET @str='SELECT * FROM #temp
WHERE SOUNDEX('+@Con1+') = SOUNDEX('''+@Val1+''')  
 AND  SOUNDEX('+@Con2+') = SOUNDEX('''+@Val2+''')'

 Execute(@str)

drop table #temp

Upvotes: 0

Related Questions