topic Re: How to Remove a Single quote in a String in QlikView

How to Remove a Single quote in a String

Tue, 04 Mar 2014 19:27:04 GMT

Hi All,

I have a requirement to do a Wildmatch on the string 'Reg'is' ,but as the string has a single quote after the letter g,its not taking the field value,have used Purgechar() function but unable to remove the single quote.

Please help me out on the function to be used.

Thanks,

Neha

Re: How to Remove a Single quote in a String

maxgro — Tue, 04 Mar 2014 19:34:31 GMT

this seems to work

load replace(col1, chr(39), '');

LOAD * INLINE [

col1Reg'is

aa''aa''aa

];

Re: How to Remove a Single quote in a String

rustyfishbones — Tue, 04 Mar 2014 19:36:29 GMT

Try REPLACE

REPLACE(YourField,CHR(39),'') as NewField

See the attached file

Re: How to Remove a Single quote in a String

Tue, 04 Mar 2014 19:37:14 GMT

While loading the data from source, remove the single quote with Replace function.

LOAD Replace(FieldName , chr(39) ,'') AS FieldName

;

SELECT * FROM TABLENAME ;

Re: How to Remove a Single quote in a String

Tue, 04 Mar 2014 19:38:20 GMT

Hey Neha,

Use

PurgeChar(Name,Chr(39)) as NewVAlue.

Here Chr(39) will give you '

Attached is a sample

Thanks

Re: How to Remove a Single quote in a String

Tue, 04 Mar 2014 19:46:23 GMT

Instead of Pulling it as a new value,can't we do it on the front end,cause I need to remove the single quote only from this single String value 'Reg'is'

Thanks,

Neha

Re: How to Remove a Single quote in a String

santharubban — Tue, 04 Mar 2014 19:55:34 GMT

YOU CAN TRY THIS

load PurgeChar(col1, '');

LOAD * INLINE [

col1Reg'is

aa'aa'aa

];

Re: How to Remove a Single quote in a String

maxgro — Tue, 04 Mar 2014 19:57:01 GMT

in listbox use an expression

=replace(col1, chr(39), '')

in chart a calculated dimension (=replace(col1, chr(39), ''))

Re: How to Remove a Single quote in a String

Tue, 04 Mar 2014 20:03:45 GMT

In that case you can do something like if(Name='Reg'&Chr(39)&'is','Regis',Name)

You can do this in a list box expression or in the backend. This will replace

Reg'is with Regis

Re: How to Remove a Single quote in a String

rustyfishbones — Tue, 04 Mar 2014 20:07:53 GMT

Yes you can use the same as a List Box Expression

REPLACE(YourField,CHR(39),'')

Re: How to Remove a Single quote in a String

Tue, 04 Mar 2014 22:06:42 GMT

Thanks Ajay,the expression worked

Re: How to Remove a Single quote in a String

Tue, 04 Mar 2014 22:11:46 GMT

And Thanks to everyone for the quick responses and throwing light on the different approaches to try.