cancel
Showing results for 
Search instead for 
Did you mean: 

REMOVE SPECIAL CHARACTER FROM A TEXT

JMANP00C
7 - Data Storage
7 - Data Storage

JMANP00C_0-1719373760587.png

Please help how to remove the special character in text for policy owner name?

5 REPLIES 5

JMANP00C
7 - Data Storage
7 - Data Storage

hope anyone can help

HamzaJ
12 - Data Integration
12 - Data Integration

Hello @JMANP00C 

I am a bit confused. Your screenshot is throwing me of 🙂 The highlighted code does not have a relation with the error you are getting when parsing the code. 

If you want to remove special characters, then I think you have to it per character. You can do this with the function replaceAll() and CHAR(). For example CHAR(13) is a linebreak (carriage return)

Hope this helps

Hamza

 

 

AssafHanina
Sisense Team Member
Sisense Team Member

hey @JMANP00C ,

It looks like you want to remove the text pattern '[^a-zA-Z ]' from the Policy Owner Name column.

The Substring function slices a string from the beginning based on the index. Instead, please consider using the Replace All function to replace the specified pattern with an empty string.

Please find an Example:

  1. Open any Elasticube Data model
  2. Add Custom Table 
  3. Paste the SQL Below and Preview the Outcome
    AssafHanina_2-1719486913659.png

select 
    example_a,
    example_b,
    example_c,
    strparts(example_a,'$',1) as remove_before, -- strsparts which remove the text before the special character 
    strparts(example_a,'$',2) as remove_after, -- strsparts which remove the text after the special character 
    SubString(example_b,5) as sub_string, -- extract the substring based on the index
    ReplaceAll(example_b,'[a-z,A-Z]','') as replace_with_empty_String, -- replace all occurroneces of the old string with new string.
    ReplaceAll(example_c,'[a-z,A-Z]','$') as replace_with_special_charcater
from 
(
select 
    'text_to_remove_with_special_charchater_$!@' as example_a, 
    'example_[a-z,A-Z]' as example_b, 
    'example_[a-z,A-Z]_test' as example_c
) a

Best Regards

Assaf

DRay
Community Team Member
Community Team Member

Hello @JMANP00C

I wanted to follow up to see if the solutions offered worked for you.

If so, please click the 'Accept as Solution' button so other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help.

Thank you.

David Raynor (DRay)

DRay
Community Team Member
Community Team Member

Hello @JMANP00C,

I wanted to follow up to see if the solutions offered worked for you.

If so, please click the 'Accept as Solution' button so other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help.

Thank you.

David Raynor (DRay)