SQL Server: Query to remove special characters from string

The following query can be used to remove special characters from a string using the STUFF function:

DECLARE @String VARCHAR(100) = 'Hello@!#$%^&*()_+ World'

SELECT STUFF(@String, PATINDEX('%[^a-zA-Z0-9 ]%', @String), 1, '') AS [CleanString]

WHILE PATINDEX('%[^a-zA-Z0-9 ]%', @String) > 0


  SET @String = STUFF(@String, PATINDEX('%[^a-zA-Z0-9 ]%', @String), 1, '')


SELECT @String AS [CleanString]

This query uses the PATINDEX function to search for any characters that are not letters, numbers, or spaces. The STUFF function then replaces these characters with an empty string. The process is repeated until all special characters are removed. The final result is stored in the @String variable.

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...