如何从SQL查询中修剪特殊字符？

trim() is used to clear all the whitespaces. Now, instead of clearing white spaces, I want to eleminate all special characters from the 'username'. Is there any function like trim() for eliminating special characters?? My sql query is like

Select value from table_name where trim(username) = 'ABCD'

and it returns null value. But there are some values related to 'ABCD' and it displays all the entries when i execute the query

select value from table_name where username like '%ABCD%'

there are nothing else like visible in 'username' field. Is there any solution for this?

Mysql does not have such kind of functionality to remove special characters. Instead you can use replace function (if you know what are special characters).

replace(your filed name,'_','')

See example :

Select value from table_name where REPLACE(trim(username$),"$","");

this if you want to replace the $ with an empty char

Try this function:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

Once you understand the code, you should see that it is relatively simple to change it to remove other characters, too. You could even make this dynamic enough to pass in your search pattern.

refer this link:https://stackoverflow.com/a/22684392/3242978