spirose
asked on
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
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
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.
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.
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
Olemo
That still appears to leave the closing ] for Married and DepID, probably a typo though.
What if there are more variables/parameters/field s involved, or the names different?
That still appears to leave the closing ] for Married and DepID, probably a typo though.
What if there are more variables/parameters/field
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.
If that is not what you had in mind, please elaborate, best by example.
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
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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This can't be done just with SQL.
You need a function to go through and extract the <varname> from [User::<varname>].
You need a function to go through and extract the <varname> from [User::<varname>].
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?