Issue with snowflake returning text from api with a red dot

Question

Does anyone know how I can remove this red dot from my data? It is causing issues, and not allowing me to replace text and cast to a date.

Answer 1

First, find out what the unicode representation of that character is. You might have to use some string functions to locate that character if you don't have it handy. Once you have it, do

select unicode('the red dot character'); 

Then replace what that unicode represents as a character

select replace(col, char(that unicode number), '');

Sample implementation in case you have trouble locating the character

set str='r2_t1_date_c_2022_?01_28';
select replace($str, char(unicode(split_part($str,'_',6))), ''); --unicode looks at the first character of a string/substring (in our case 6th underscore delimited part)

Answer 2

Most of characters repreented by "red dots" are non-printable:

SELECT LISTAGG(CONCAT(seq8(), CHAR(seq8())), ',') WITHIN GROUP (ORDER BY seq8())
FROM TABLE(GENERATOR( ROWCOUNT => 1000 ));