Remove Ascii Extended Characters 128 Onwards (Sql)

How do I remove extended ASCII characters from a string in T-SQL?

OK, give this a try. It seems the same issue they have. Anyway you need to modify it based on your requirements.

CREATE FUNCTION RemoveNonASCII 
(
    @nstring nvarchar(255)
)
RETURNS varchar(255)
AS
BEGIN

    DECLARE @Result varchar(255)
    SET @Result = ''

    DECLARE @nchar nvarchar(1)
    DECLARE @position int

    SET @position = 1
    WHILE @position <= LEN(@nstring)
    BEGIN
        SET @nchar = SUBSTRING(@nstring, @position, 1)
        --Unicode & ASCII are the same from 1 to 255.
        --Only Unicode goes beyond 255
        --0 to 31 are non-printable characters
        IF UNICODE(@nchar) between 32 and 255
            SET @Result = @Result + @nchar
        SET @position = @position + 1
    END

    RETURN @Result

END
GO

Check it out at SqlServerCentral

How to remove characters from a string when flattening?

I think it's about the missing escape characters:

with MyData as (
select ['[''Customer service'', ''Ux/Ui'']'] as label )
select regexp_replace(value::varchar, '[\\[\\]'']', '') as label_cleaned
from MyData,
lateral flatten(input => label)

As you can see I added backslash symbols.

Check If the string contains accented characters in SQL?

SQL Fiddle: http://sqlfiddle.com/#!6/9eecb7d/1607

declare @a nvarchar(32) = 'àéêöhello!'
declare @b nvarchar(32) = 'aeeohello!'

select case 
    when (cast(@a as varchar(32)) collate SQL_Latin1_General_Cp1251_CS_AS) = @a 
    then 0 
    else 1 
end HasSpecialChars

select case 
    when (cast(@b as varchar(32)) collate SQL_Latin1_General_Cp1251_CS_AS) = @b 
    then 0 
    else 1 
end HasSpecialChars

(based on solution here: How can I remove accents on a string?)

How to make temporary column without punctuation in SQL

select id, tax, replace(replace(replace(tax, '.', ''), '-', ''), ',', '')
from mytable

SQL Patindex / Regex - Match where there are 4 or less characters between 2 apostrophes

If you don't care about the particular order of the words which are retained after filtering off words 4 characters or less, you could use STRING_SPLIT and STRING_AGG:

WITH cte AS (
    SELECT id, value
    FROM yourTable
    CROSS APPLY STRING_SPLIT(val, ',')
)

SELECT id, STRING_AGG(value, ',') AS val
FROM cte
WHERE LEN(value) > 6
GROUP BY id;

Demo

How to use Unicode string for primary key in SQL Server

The SQL_Latin1_CP1 collation does not cater for all Unicode codepoints.

select N'test' collate SQL_Latin1_General_CP1_CI_AS
union
select N'test' + nchar(5028) + nchar(5044) + nchar(5049)
-- result: 1 row

Use the newer ones for SQL Server 2008 onwards, e.g.

select N'test' collate Latin1_General_100_CI_AS
union
select N'test' + nchar(5028) + nchar(5044) + nchar(5049)
-- result: 2 rows

The unicode characters you are using fall within the 16-bit character range, so supplementary characters don't come into play yet.

---

Related Topics