how do i remove punctuation and # characters from data being pulled from table into flat file. for example name contains DELL.JR i need this to just show as DELL JR

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’m not aware of a standard SQL way to accomplish this. As far as I know, you would have to use a HLL (high-level language) or UDF (user defined function). Implementation of these would depend on your platform.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Mt69
    you can use nested REPLACE calls in your SELECT query. Example: Replace all # and . : SELECT REPLACE(REPLACE(MY_STRING,'#',''),'.','') FROM MY_TABLE
    0 pointsBadges:
  • BenjiT
    If this is SQL Server you can use the REPLACE function. For example SELECT REPLACE ('THIS# IS #A STRING WITH PUNCTUATION#.', '#', '') AS SOMETEXT This would result in as SOMETEXT ------------------------------------------------------- THIS IS A STRING WITH PUNCTUATION. I demonstrated this with a literal string. You can replace the string with a column. The problem is that you would have to use embedded REPLACE functions for each character you wish to exclude. Here is an example removing ! and # from the column SOMETEXT in the table foo SELECT REPLACE(REPLACE(SOMETEXT, '!',''), '#','') FROM foo As you can see, you can nest as many as you have special characters. This method is not elegant; but it will work. Additionally, if you create a view with the formula to remove these characters then you can export the data directly using BCP or DTS. Finally, you could use a VBScript method to remove these characters using DTS. See SQL Server Books Online for DTS and VBScript. If you are not using SQL Server then you have posted this question improperly. Cheers, Ben
    0 pointsBadges:
  • Welcome
    Hi, If your database is Oracle, you may also use the TRANSLATE function, which is a shorter way. For example: TRANSLATE('XYZ#ABC.','A#.','A') returns XYZABC. The general format is TRANSLATE(mystring, from_charlist, to_charlist), which means that each character of mystring that appears in the "from_charlist" is translated into its correspondent character in the "to_charlist". In case that the "from_charlist" is longer than the "to_charlist", the remaining characters in the "to_charlist" are translated to NULL, which means that they are simply removed from the string. In the sample above I used 'A' as the first character in both "from_charlist" and "to_charlist" for avoid the "to_charlist" being NULL, in which case the whole result would have turned to NULL. Instead of 'A' you may use any character different from those that are to be removed. Hope this helps. Best rgds, Iudith
    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: