single set based query

[dileep.gare]

HI

i have requirement can any one resolve it

there are there strings "sample","pa","vx"

now check 2ndstr 1st character with 1st string if character present then replace the first string with thrid string (second string char index of first character)
ex: take 2nd str first char "P"
Check with 1st string if there replace the 1st string with "P" char index OF thrid string. i.e 2ndstr(0)=3rdstr(0);
now samvle
repeat the same step
string must be "sxmvle"

one more example to get understand "as400",'0la","ssv"
step1: result as4ss
step2: result as4ss
Step3:result vs4ss


Note: we should not use variables,looping,table,arrays etc it must be in single set based query.



Thanks

[tomliotta]

Re: single set based query

Note: we should not use variables,looping,table,arrays etc it must be in single set based query.

That's an odd work requirement. It makes it feel like an academic assignment. What have you tried?

[dileep.gare]

Re: single set based query

hi
i used Translate function to do that...is there any alternative way to do

[tomliotta]

Re: single set based query

The TRANSLATE() function should fit perfectly. I don't know of a better way. Both of your examples could be done in a single query together.

[dileep]

Re: single set based query

I dont believe that i have another account registered long back ago and this is my first reply from this account further i wont be using this account.

Thanks for your confirmation Tomliotta

I thought that there may be alternative way to approach without using any variable,arrays,etc,

[tomliotta]

Re: single set based query

Yes, there are often alternatives. I can imagine nested REPLACE() functions in some circumstances for example. But your examples have two-character values in one case and three-character values in the other. If the search and replace lengths vary from example to example, nesting gets less predictable.

[dileep.gare]

Re: single set based query

yeah, what u said is correct we cant predict every time the length of the string to be same. it may change.
in that case replace function might fail i think.

i want the fixed query that should work for every string, because strings wont be same every time

Thanks
Dileep