To remove the special character from the column using recursive CTE method

Question

I have a table like this :

     |----------------------------|
     |  CardCorporateName         |
     |----------------------------|
     | ~$%/*dfgdggf/*(            |
     |---------------------       |
     | ^^&@~`58964sdhfdk-+*/-=-0  |
     |----------------------------|

I need to remove the special character from the column CardCorporateName and my Output should be like this :

   |----------------------------|
   |  CardCorporateName         |
   |----------------------------|
   | dfgdggf                    |
   |----------------------------|
   | 58964sdhfdk                |
   |----------------------------|

Is there any method to remove the special character from the column using recursive CTE method?

Are special characters anything that's not alphanumeric; or are there any other safe chars? — JohnLBevan, Jan 29 '18 at 09:16
@JohnLBevan it should not be alphanumeric..thats all .(need to avoid characters that are not alphanumeric) — DeadCat, Jan 29 '18 at 09:20
@DeadCat if you only want alpha numeric please see linked post — WhatsThePoint, Jan 29 '18 at 09:21
@WhatsThePoint but its using via function..is there any method using recursive CTE method? (question edited) — DeadCat, Jan 29 '18 at 09:27
@DeadCat why are you favouring a recursive CTE over a function? — WhatsThePoint, Jan 29 '18 at 09:28
using a function its time consuming..using CTE would be better as my table has huge load of data. — DeadCat, Jan 29 '18 at 09:32

score -1 · Answer 1 · edited Jan 29 '18 at 11:01

-1

SELECT REPLACE(REPLACE('~$%/*dfgdggf/*(', '~$%/*', ''),'/*(','')

SELECT REPLACE(REPLACE('^^&@~`58964sdhfdk-+*/-=-0','^^&@~`',''),'-+*/-=-0','')

edited Jan 29 '18 at 11:01

Abdul Rasheed

6,486
4
32
48

answered Jan 29 '18 at 09:29

Kalirajan

42
8

please explain your code – GGO Jan 29 '18 at 10:27

To remove the special character from the column using recursive CTE method

1 Answers1