How to replace a special character or punctuation in a string in SQL Server

Question

I am prepping our data to make it a URL. But I've been into a lot of obstacle. If it's just a punctuation or some special characters, I can do nested REPLACE Functions and that is not a problem. What I'm dealing with right now is how to fully make it right. Like the right plural form and such :)

See example below:
Looks like inches are being used even if it's just 1. Same thing with feet
So what I want is how can I correct this. If it is 1, then inch or foot not inches or feet.

I also need to know how to figure

3M 16"x25"x1" Filtrete® Dust Reduction Filter
to become
3m-16-inches-x-25-inches-x-1-inches-filtrete-dust-reduction-filter-9790692

Without affecting those that have actual 'x' on their name.

Answer 1

If you already have a script that outputs the shown data, did you consider a final fix like REPLACE( revised_url_key, '-1-inches', '-1-inch') and similarly for “-1-feet”?

Do you need a more complex script that builds revised_url_key from name?

Although, the symbols can be included into URL too. For example, the apostrophe can be represented as %27, the space: as %20 or +.

You can also imply some C# functions into these transformations, where you can use Regular Expressions.

Answer 2

Try this:

DECLARE @name varchar(100) = '3M 16"x25"x1" Filtrete® Dust Reduction Filter';
SELECT LOWER(REPLACE(REPLACE(REPLACE(REPLACE(@name, '"x', '-inches-x-'), '" ', '-inches-'), CHAR(174), ''), ' ', '-'));

But not sure where 9790692 comes from.

Answer 3

Hi @Anonymous ，

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

DECLARE @name varchar(100) = '3M 16"x25"x1" Filtrete® Dust Reduction Filter';  
SELECT LOWER(REPLACE(REPLACE(REPLACE(REPLACE(@name, '"x', '-inch-x-'),'" ', '-inch-'), '®', ''),' ','-'))  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

One more ask

Can I use a wildcard in a REPLACE function?

For example:
Steel Resilient Channel 1/2x12' 25ga

How can I have that 'x' to be -x-
I am trying to avoid to pick the wrong 'x'. Like those in the actual words.

How can I replace those 'x' that are in between the numbers?

Answer 5

I guess my real question is that, can you use the REPLACE function to replace multiple characters scattered all over?

Like I need to replace all of the characters below with a dash

& ® ™ + • · ( )