SQL query syntax and logic

DECLARE @TestString varchar(MAX)
 
SET @TestString = 'WHERE [DeptID] = @[User::DeptID] Or ( [Status] = @[User::Married] And  [BirthDate] > @[User::DOB] ) Or  [DOB] Is Not Null'
 
--In @TestString, there could be any # of user parameters in the format @[User::NameOfParam] where NameOfParam is a varaible
 
I need to update this field to:
 
'WHERE [DeptID] = @DeptID Or ( [Status] = @Married And  [BirthDate] > @DOB ) Or  [DOB] Is Not Null'
 
--Thus only the first ']' immediately after any recurring pattern '[User::' should be eliminated
 
spiroseAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The dynamic solution is:
declare @TestString varchar(1000)
SET @TestString = 'WHERE [DeptID] = @[User::DeptID] Or ( [Status] = @[User::Married] And  [BirthDate] > @[User::DOB] ) Or  [DOB] Is Not Null'

declare @pos1 integer, @pos2 integer;
declare @varstr varchar(10)
set @varstr = '@[User::'

while (charindex( @varstr , @TestString) > 0)
begin
  set @pos1 = charindex(@varstr, @TestString)
  set @pos2 = charindex(']', @TestString, @pos1)
  set @TestString = left(@TestString, @pos1-1) + '@' + substring(@TestString, @pos1+len(@varstr), @pos2-@pos1-len(@varstr)) + substring(@TestString, @pos2+1, 8000) 
  print @TestString
end

Open in new window

After that, your @TestString should contain the modified SQL statement.
0
 
NorieVBA ExpertCommented:
I don't think you can do this without a function.

Doing the first replacement wouldn't be a problem, but then you would need to repeat on the result of that replacement for the 2nd, then repeat for the third.

I suppose that wouldn't be too bad for 3 replacements, but what if there are more?
0
 
DALSOMCommented:
Hi,
Can you write what is your input and output data of this where!
I starting think it is just a parentesis missing (grouping by parentesis) and no more.

Bye,
Dalsom.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It can be done without a function, but it is kind of tricky:
declare @TestString varchar(1000)
SET @TestString = 'WHERE [DeptID] = @[User::DeptID] Or ( [Status] = @[User::Married] And  [BirthDate] > @[User::DOB] ) Or  [DOB] Is Not Null'
select @teststring = replace(@TestString, pattern, result) from (
   select '@[User::Married' pattern, '@Married' result
   union all
   select '@[User::DeptID', '@DeptID'
   union all
   select '@[User::DOB]', '@DOB'
) list;
print @teststring

Open in new window

0
 
NorieVBA ExpertCommented:
Olemo

That still appears to leave the closing ] for Married and DepID, probably a typo though.

What if there are more variables/parameters/fields involved, or the names different?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
WIth default means you cannot use pattern matching and regular expressions, as you would require here. So coding it with a function or directly with procedural code is the way to go, if you got a lot of possible variables.
The remaining closing square bracket is a typo, indeed, but easy to fix. We could also simplify to better show the pattern.
declare @TestString varchar(1000)
SET @TestString = 'WHERE [DeptID] = @[User::DeptID] Or ( [Status] = @[User::Married] And  [BirthDate] > @[User::DOB] ) Or  [DOB] Is Not Null'
select @teststring = replace(@TestString, '@[User::' + var + ']', '@' +var) from (
   select 'Married' var
   union all
   select 'DeptID'
   union all
   select 'DOB'
) list;
print @teststring

Open in new window

which looks better. You could use a table containing all variable names allowed, and extend that to your likings and needs. If you need to replace something like '@[User::Xyz]' with '@ZZZ', you have no other choice than to provide all allowed combinations either as ad-hoc view as shown by me, or as a translation table to use instead, and so on. The solution is flexible, but needs to have a full list of possible replacements.
If that is not what you had in mind, please elaborate, best by example.
0
 
spiroseAuthor Commented:
This solution hardcodes the field values which is not why we want. Thename of variables and # is dynamic. What we are trying to do here is look for the pattern [User::   And only the first ']' immediately after any recurring pattern '[User::' should be eliminated
 
0
 
NorieVBA ExpertCommented:
This can't be done just with SQL.

You need a function to go through and extract the <varname> from [User::<varname>].
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.