How to remove space before a special character in sql

Pinku

I have a string where there is a space before the Special character in between the word sustainability and -energy.
DECLARE @Title AS VARCHAR(max)='sustainability -energy and power' so I want to make it like 'sustainability-energy and power' .
It should only remove the space before that special character(-). the special character can be any thing.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari1y ago

Accepted answer

Hi Pinku,

You can achieve this by using the REPLACE method using PATINDEX functions. Here is the query:

DECLARE @Title AS VARCHAR(MAX) = 'sustainability -energy and power'

SET @Title = REPLACE(@Title, ' ' + SUBSTRING(@Title, PATINDEX('% [^a-zA-Z0-9]%', @Title) + 1, 1), SUBSTRING(@Title, PATINDEX('% [^a-zA-Z0-9]%', @Title) + 1, 1))

SELECT @Title AS UpdatedTitle

Thank you.

Pinku1y ago
Thanks Imran Its working
+3