0

sql server 2008

I have a data in a column something like

"Brake pad kit, disc brake"
/Brake disk (sold separately).
"The belt pulley, crankshaft"
 Fuel Pump

the special character are "",space,/

i want to remove any special character or space present in begining or end of the string.

is this possible to do in sql, not sure. Please share your thoughts.

Rohini Mathur
  • 431
  • 1
  • 5
  • 19
  • 3
    See http://stackoverflow.com/questions/1289178/search-column-in-sql-database-ignoring-special-characters – dr0i Jul 07 '16 at 08:56

1 Answers1

2

Here is one way to do it using String functions

DECLARE @str VARCHAR(200)= '"The belt pulley, crankshaft"' 

SELECT Reverse(CASE 
                 WHEN LEFT(Reverse(scd_str), 1) LIKE '[A-Z]' OR LEFT(Reverse(scd_str), 1) LIKE '[a-z]' THEN Reverse(scd_str) 
                 ELSE Substring(Reverse(scd_str), 2, Len(Reverse(scd_str))) 
               END) 
FROM   (SELECT CASE 
                 WHEN LEFT(string, 1) LIKE '[A-Z]' OR LEFT(string, 1) LIKE '[a-z]' THEN string 
                 ELSE Substring(string, 2, Len(string)) 
               END AS Scd_Str 
        FROM   (SELECT Rtrim(Ltrim(@str)) AS string) A) B 

Result : The belt pulley, crankshaft

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172