How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Steve Your Own Question
Steve, Consultant
Category: Programming
Satisfied Customers: 290
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
Type Your Programming Question Here...
Steve is online now
A new question is answered every 9 seconds

I want to remove special characters from a column in

Customer Question

I want to remove special characters from a column in a SQL Server select statement without using a separate replace for each character. Any advise would be truly appreciated
Submitted: 1 year ago.
Category: Programming
Expert:  Steve replied 1 year ago.
If you don't want to do nested REPLACE statements as in your example, you need to create a function to do this. It's usually easier to specify the characters you don't want to replace, rather than trying to enumerate all the possible special characters you want to get rid of. For example: CREATE FUNCTION [mydb].[eliminate](@String VarChar(500))Returns VarChar(500)ASBEGIN DECLARE @KeepThese AS VarChar(100) SET @KeepThese = '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789]%' WHILE PATINDEX(@KeepThese, @String) > 0 SET @String = Stuff(@String, PATINDEX(@KeepThese, @String), 1, '') RETURN @StringEnd;And then, something similar to:SELECT mydb.eliminate(mydb.field) AS fieldname FROM mydb; If this answers your question, please accept the answer and rate me accordingly. If it doesn't, please chat me back and I'll find the right answer for you. Thanks!
Expert:  Steve replied 1 year ago.
Hi there. I was just following up with you to see if the function worked to strip out the characters? If it did, can you please accept my answer and rate me accordingly? I don't get paid anything for writing this code for you unless you accept the answer. Thanks!