string function for UTF-8 bytes

[Former Member]

I have a table column of the type TEXT that needs to be parsed with all special dec characters with '?' character. Please provide some information as I am not sure how to handle "UTF-8" bytes.

pseudo code:

select function_name(TEXT_columnName) from table_name;

create function_name(@TEXT_columnname TEXT) returns TEXT as
begin
...
final StringBuilder sb = new StringBuilder();
        try {
            for (byte b : s.getBytes("UTF-8")) {
                if (b < 32 || b > 254) {
                    continue;
                }
                sb.append((char) b);
            }
        } catch (UnsupportedEncodingException e) {
            LOG.error("Unsupported Encoding", e);
        }
        return sb.toString();
end

[MarkCulp · Accepted solution]

It looks like you are simply trying to find strings that contain non-ascii characters? If this is correct then look at the patindex function and regexp search condition.

Example: Here is a snippet of code that will raise an exception if a non-printable non-7-bit-ascii character is found in the string @str.

begin
    declare myexception exception for sqlstate 99001;
    if @str not regexp '[[:ascii:]]*' then
        signal myexception;
    end if;
end;

HTH

[Former Member · Accepted solution]

Thanks for your info!