How to Replace Multiple Characters in SQL?

by

One useful trick in SQL is the ability use @var = function(...) to assign a value. If you have multiple records in your record set, your var is assigned multiple times with side-effects:

declare @badStrings table (item varchar(50))

INSERT INTO @badStrings(item)
SELECT '>' UNION ALL
SELECT '<' UNION ALL
SELECT '(' UNION ALL
SELECT ')' UNION ALL
SELECT '!' UNION ALL
SELECT '?' UNION ALL
SELECT '@'

declare @testString varchar(100), @newString varchar(100)

set @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'
set @newString = @testString

SELECT @newString = Replace(@newString, item, '') FROM @badStrings

select @newString -- returns 'Juliet ro0zs my s0xrzone'

More Related Contents:

  1. Why do SQL Server Scalar-valued functions get slower?
  2. Insert an image in a column of a table that already has 5 columns
  3. Get top 1 row of each group
  4. Referring to a Column Alias in a WHERE Clause
  5. How to SELECT FROM stored procedure
  6. Convert Month Number to Month Name Function in SQL
  7. How to avoid the “divide by zero” error in SQL?
  8. Function vs. Stored Procedure in SQL Server
  9. SET versus SELECT when assigning variables?
  10. Is there any difference between IS NULL and =NULL
  11. Generate a resultset of incrementing dates in TSQL
  12. Real life example, when to use OUTER / CROSS APPLY in SQL
  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. Better techniques for trimming leading zeros in SQL Server?
  16. How to print VARCHAR(MAX) using Print Statement?
  17. SQL statement to select all rows from previous day
  18. What’s the best way to select the minimum value from several columns?
  19. Concatenate values based on ID
  20. Splitting delimited values in a SQL column into multiple rows
  21. varbinary to string on SQL Server
  22. SQL : in clause in stored procedure:how to pass values
  23. SQL GROUP BY CASE statement with aggregate function
  24. separate comma separated values and store in table in sql server
  25. Best way to do nested case statement logic in SQL Server
  26. Restrict an SQL Server connection to a specific IP address
  27. SQL Server 2005 ROW_NUMBER() without ORDER BY
  28. Can an INNER JOIN offer better performance than EXISTS
  29. SQL server identity column values start at 0 instead of 1
  30. How to compare dates in SQL Server

Leave a Comment