Removing the JUNK Characters

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  April 21, 2008 at 4:33 am

  #71932

  Dear All,

  I want to remove the JUNK characters from Remarks column.

  Say for example,

  Create table Emp

  (

  Eno int,

  Salary money,

  Remarks

  )

  Insert into Emp

  Select 1,5000,'Bon#us&@amount'

  union all

  select 2,8000,'Previous,..,month^&^salary'

  Union all

  select 3,10000,',Leave,.Travel allowance'

  Select * from Emp

  Output

  ...........................

  Eno Salary Remarks

  ............................

  1 5000 Bon#us&@amount

  2 8000 Previous,..,month^&^salary

  3 10000 ,Leave,.Travel allowance

  i want to remove the JUNK characters like #&@,.^.

  Expected Output :

  ...........................

  Eno Salary Remarks

  ............................

  1 5000 Bonus amount

  2 8000 Previousmonth salary

  3 10000 LeaveTravel allowance

  Thanks in advance.

  karthik

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  April 21, 2008 at 4:54 am

  #804848

  On http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67997 there is a function that removes these characters.

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  April 21, 2008 at 5:49 am

  #804880

  But Andras i dont want to use any functions. Because i dont have the permission to create functions.

  karthik

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  April 21, 2008 at 6:03 am

  #804890

  karthikeyan (4/21/2008)

  ---

  But Andras i dont want to use any functions. Because i dont have the permission to create functions.

  That makes it more tricky 🙂 Before trying to find a T-SQL solution in this case, what are you doing with the returned data? Are you processing it in an application? It is much more easier to clean your data on the client (where you can use regular expressions, etc).

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  April 21, 2008 at 6:12 am

  #804899

  karthikeyan (4/21/2008)

  ---

  But Andras i dont want to use any functions. Because i dont have the permission to create functions.

  Yes. Tricky. Maybe you can do something along these lines...?

  select 'start' --needed to set @@rowcount > 0

  while @@rowcount > 0

  update Emp set Remarks = stuff(Remarks, patindex('%[^a-zA-Z ]%', Remarks), 1, '')

  from Emp where Remarks like '%[^a-zA-Z ]%'

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.