How to remove accents and all chars a..z in sql-server?

by

The best way to achieve this is very simple and efficient :

SELECT 'àéêöhello!' Collate SQL_Latin1_General_CP1253_CI_AI

which outputs ‘aeeohello!’

The string must not be unicode. If you have a nvarchar just cast it to varchar before using the collate.

Here is a function that answers the OP needs :

create function [dbo].[RemoveExtraChars] ( @p_OriginalString varchar(50) )
returns varchar(50) as
begin

  declare @i int = 1;  -- must start from 1, as SubString is 1-based
  declare @OriginalString varchar(100) = @p_OriginalString Collate SQL_Latin1_General_CP1253_CI_AI;
  declare @ModifiedString varchar(100) = '';

  while @i <= Len(@OriginalString)
  begin
    if SubString(@OriginalString, @i, 1) like '[a-Z]'
    begin
      set @ModifiedString = @ModifiedString + SubString(@OriginalString, @i, 1);
    end
    set @i = @i + 1;
  end

  return @ModifiedString

end

Then, the command:

select dbo.RemoveExtraChars('aèàç=.32s df')

outputs

aeacsdf

More Related Contents:

  1. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  2. Check if table exists in SQL Server
  3. Sql Server string to date conversion
  4. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  5. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  6. Parameter Sniffing (or Spoofing) in SQL Server
  7. How to get Time from DateTime format in SQL?
  8. SQL Server String or binary data would be truncated
  9. Datatype for storing ip address in SQL Server
  10. How to change identity column values programmatically?
  11. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  12. How much size “Null” value takes in SQL Server
  13. best way to convert and validate a date string
  14. SQL Server ORDER BY date and nulls last
  15. Find non-ASCII characters in varchar columns using SQL Server
  16. WHERE clause on SQL Server “Text” data type
  17. SQL Server error on update command – “A severe error occurred on the current command”
  18. Aggregate bitwise-OR in a subquery
  19. SQL server schema and default schema
  20. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  21. FOR XML PATH(”): Escaping “special” characters
  22. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  23. Does query plan optimizer works well with joined/filtered table-valued functions?
  24. What are row, page and table locks? And when they are acquired?
  25. @@ERROR and/or TRY – CATCH
  26. Is it possible to select sql server data using column ordinal position
  27. Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)
  28. SQL server select distinct rows using most recent value only
  29. How to store directory / hierarchy / tree structure in the database?
  30. Eliminate and reduce overlapping date ranges

Leave a Comment