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

by

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

More Related Contents:

  1. How to split a single column values to multiple column values?
  2. Better techniques for trimming leading zeros in SQL Server?
  3. How do I UPDATE from a SELECT in SQL Server?
  4. How to return only the Date from a SQL Server DateTime datatype
  5. How to calculate age (in years) based on Date of Birth and getDate()
  6. CROSS JOIN vs INNER JOIN in SQL
  7. Referring to a Column Alias in a WHERE Clause
  8. Convert Month Number to Month Name Function in SQL
  9. Function vs. Stored Procedure in SQL Server
  10. Limit on the WHERE col IN (…) condition
  11. Removing leading zeroes from a field in a SQL statement
  12. converting Epoch timestamp to sql server(human readable format)
  13. Getting result of dynamic SQL into a variable for sql-server
  14. How to get difference between two rows for a column field?
  15. What’s the best way to select the minimum value from several columns?
  16. Counting DISTINCT over multiple columns
  17. SQL Server SELECT into existing table
  18. Why use Select Top 100 Percent?
  19. How to check if a stored procedure exists before creating it
  20. Correct use of transactions in SQL Server
  21. SQL Server converting varbinary to string
  22. Find the real column name of an alias used in a view?
  23. How to get the identity of an inserted row?
  24. How to create a pivot query in sql server without aggregate function
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. SQL Server FOR XML Path make repeating nodes
  27. SQL Server SELECT INTO @variable?
  28. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  29. Select a column if other column is null
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment