replace and in string

[G2]

I have below string 

field1='abc' and field2=3 and field3 in('abc','abc and cde') and field4='a and b'

and I need to convert this as below 

field1='abc' & field2=3 &field3 in('abc','abc and cde') & field4='a and b'

[SerhanKaraer]

Hello G2,

Does this replacement solve your problem?

replace('field1=''abc'' and field2=3 and field3 in(''abc'',''abc and cde'') and field4=''a and b''','and field','& field')

[stevedark]

I presume it is not always exactly that string that you need to replace? If so you would just do a replace of the whole lot with the whole replacement.

Is it that you would like to replace the word and with ampersand, as long as the and does not fall within single quotes, for any given string?

What is the context for this requirement, as it may make a big difference to how it is tackled.

It's not straightforward (as you probably already know) and may require a string walk (i.e. read each character at a time and decide what to do), but this will be massively processor hungry if you are doing it over multiple rows.

My best guess at the moment is something like:

replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(
     chr(39) & ' and', chr(39) & ' &'),
     ') and', ') &'),
     '1 and', '1 &'),
     '2 and', '2 &'),
     '3 and', '3 &'),
     '4 and', '4 &'),
     '5 and', '5 &'),
     '6 and', '6 &'),
     '7 and', '7 &'),
     '8 and', '8 &'),
     '9 and', '9 &'),
     '0 and', '0 &')

That is assuming that the word and will only ever come after a bracket, quote or number outside of quotes, and only ever after an alpha inside of quotes.

[G2]

Thanks to Both of you for your reply.

But let me put my requirement in more detail. I have a field where I have sql statement and I want to derive new field as mentioned in requirement using where clause of sql statement.

Basically I have 'and' keyword as concatenation operator between two fields in where clause and also have 'and' as value. and as per my requirement I want to 'and' which is as concatenation operator as & and and as value of field should remain as and only. 

Please check the bold portion in below table and should return as and and rest of the and should convert as '&'

Sr.No	Source SQL	requirement
1	select sum(amount) from Table2 where record_number = 4 and currency = 'USD' and date=2020-09-30	record_number=4&currency=USD&date=2020-09-30
2	select sum(amount) from Table2 where record_number = 10 and currency = 'USD' and date=2020-09-30	record_number=10&currency=USD&date=2020-09-30
4	select sum(amount) from Table1 where record_number = 5 and currency = 'USD' and date=2020-5-11	record_number=5&currency=USD&date=2020-5-11
5	select sum(amount) from Table3 where date = '$date' and record_number = 4 and code = 'abc' and segment = 'SUB' and product='pr1 and pr2'	date=2121-09-30&record_number=4&code=abc&segment=SUB&product=pr1 and pr2
6	select sum(amount) from Table3 where date =date=2121-09-30 and record_number = 5 and code = 'abc' and segment in ('IWM','SRU','SUB','GM and AP','Cor_Centr')	date=2121-09-30&record_number=5&code=abc&segment=IWM,SRU,SUB,GM and AP,Cor_Cntr

[SerhanKaraer]

Hello,

Now this seems a query parsing problem.

Query engines does parse during execution, if you have access such data it would be a nicer solution.

As far as I understand your need, I prepared a replacement procedure without using regex.

I hope this is what you need for:

sub replaceStatement(tablename,seperator,sepReplacement)

$(tablename)_New:
NoConcatenate
// unites part with new seperator
LOAD sqlstatement,concat(part,'$(sepReplacement)',recno) as filter GROUP BY sqlstatement;
// enumerates lines of part for next step
LOAD sqlstatement,part,recNo() as recno where mod(substringcount(part,chr(39)),2)=0;
// if seperator is in a string, unites lines of part until there is no string split wrongly
LOAD sqlstatement,if(quoteCount=0,part,if(previous(quoteCount)=0,part,previous(part) & '$(seperator)' & part)) as part;
// checks whether seperator is in a string, or not
LOAD sqlstatement,part,mod(substringcount(part,chr(39)),2) as quoteCount;
// forms parts disregarding the seperator
LOAD sqlstatement,mid(sqlstatement, start, count) as part, start, count;
// disregards seperator string in parts
LOAD sqlstatement,if(newstatement=1,start,start+len('$(seperator)')) as start, if(newstatement=1,count,count-len('$(seperator)')) as count;
// determines start of each part in sqlstatement and characters to count in sqlstatement till the start of next part
LOAD sqlstatement,if(isnull(previous(start)) or newstatement=1,1,previous(start)) as start, if(start=0,len(sqlstatement)+1,start)-if(isnull(previous(start)),1,previous(start)) as count,newstatement;
// splits sqlstatement into lines by using seperator variable
LOAD sqlstatement,if(previous(sqlstatement)<>sqlstatement,1,0) as newstatement,Index(sqlstatement,'$(seperator)', IterNo()) as start while IterNo() <= substringCount(sqlstatement,'$(seperator)')+1;
LOAD * Resident $(tablename);

drop field sqlstatement from $(tablename)_New;
drop table $(tablename);
rename table $(tablename)_New to $(tablename);
rename field filter to sqlstatement;

end sub

Input:
LOAD * INLINE [
sqlstatement
"record_number = 10 and currency = 'USD' and date=2020-09-30"
"record_number = 5 and currency = 'USD' and date=2020-5-11"
"date = '$date' and record_number = 4 and code = 'abc' and segment = 'SUB' and product='pr1 and pr2'"
"date =date=2121-09-30 and record_number = 5 or code = 'abc' and segment in ('IWM','SRU','SUB','GM and AP','Cor_Centr')"
];

call replaceStatement('Input',' and ',' & ');
call replaceStatement('Input',' or ', ' || ');