none
how to replace non ascii charactes with empty in postgres RRS feed

  • Question

  • Hi 
     I have one doubt in postgresql, how to replace non ascii character with empty values in postgresql
    table :Emp
    address
    Îlt-t-Fce
    Āddī Ārkʿay
    ʿAlūla

    based on above data i wantoutput like below 
    Ilt-t-Fce
    AddAArkEay
    EAlAla

    I tried like below 
    SELECT replace(addres,'%[^a-Z0-9, ]%',' ')
    FROM emp
    WHERE address like '%[^a-Z0-9, ]%'
    or 
    select   replace(addres,'^[^[:ascii:]]',' ') from  emp where  address ~ '^[^[:ascii:]]' 

    above two queris are not giving expected result
    can you please tell me how to write query to replace for non ascci chareater with empty values in postgresql

    Wednesday, April 24, 2019 3:13 AM

All replies

  • Hi BalaKrishna,

    Thank for your feedback. I believe this cannot be handled with single command. You need create a function to take care of this.

    Could you please refer below StackOverFlow threads. I believe these might help you.

    https://stackoverflow.com/questions/36176985/remove-ascii-extended-characters-128-onwards-sql/36177685#36177685

    https://stackoverflow.com/questions/15259622/how-do-i-remove-extended-ascii-characters-from-a-string-in-t-sql

    Hope it helps.

    Wednesday, April 24, 2019 6:23 AM
    Moderator
  • Hi BalaKrishna

    I hope the information provided for your concern is helpful. Please "Mark as answer" if it was helpful, so that it can help the other users with same problem.

    If you need more information, please let us know.

    Friday, April 26, 2019 8:10 AM
    Moderator