Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Read about the latest Qlik Community enhancements on the Community News blog!
Showing results for 
Search instead for 
Did you mean: 

replace and in string

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'


4 Replies
Contributor III
Contributor III

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')


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:

     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.




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.NoSource SQLrequirement
1select sum(amount)
from Table2 where record_number = 4 and currency = 'USD' and date=2020-09-30
2select sum(amount)
from Table2 where record_number = 10 and currency = 'USD' and date=2020-09-30
4select sum(amount)
from Table1 where record_number = 5 and currency = 'USD' and date=2020-5-11
5select 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
6select 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


Contributor III
Contributor III


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)

// 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

"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 ', ' || ');