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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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
Qlemo"Batchelor", 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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NorieAnalyst Assistant Commented:
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
Qlemo"Batchelor", 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
Qlemo"Batchelor", 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
This can't be done just with SQL.

You need a function to go through and extract the <varname> from [User::<varname>].
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.