none
how to remove special characters in a particular column

    Question

  • Hello,

    i have a csv feed and i load it into a sql table (the sql table has all varchar data type fields)

    feed data looks like (just sampled 2 rows but my file has thousands of like this)
    "K" "AIF" "AMERICAN IND FORCE" "FRI" "EXAMP" "133" "DISPLAY" "505250" "MEDIA INC." 03/01/10" "INV31202" ".00" ".00" "0.00" "ALLO" ;
    "K" "AIF" "AMERICAN IND FORCE" "THU" "TRTH" "123" "MUN MARCH 2010" "505099" "SOCIALTYZE" "03/01/10" "1308" "40,282.32" "40,282.32" "45564.64" "DIS" ;


    some times i got some special characters in my table column (example: in my invoice no column some time i do have # or ! kind of invalid characters ) so how can i remove some kind of special characters in my column
    once it is eliminated then i can write it to new table (with correct data format such as integer)

    could some one please tell me how can i remove special character in a column of particular table

    thanks in advance
    asita

    Tuesday, May 25, 2010 4:08 PM

Answers

  • /*********************************
    Removes any characters from
    @myString that do not meet the
    provided criteria.
    *********************************/
    CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
    RETURNS varchar(500) AS
    BEGIN

    While @myString like '%[^' + @validChars + ']%'
    Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

    Return @myString
    END
    Go

    Declare @testStr varchar(1000),
    @i int

    Set @i = 1
    while @i < 255
    Select
    @TestStr = isnull(@TestStr,'') + isnull(char(@i),''),
    @i = @i + 1

    Select @TestStr
    Select dbo.GetCharacters(@TestStr,'a-z')
    Select dbo.GetCharacters(@TestStr,'0-9')
    Select dbo.GetCharacters(@TestStr,'0-9a-z')
    Select dbo.GetCharacters(@TestStr,'02468bferlki')
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 11:56 AM
  • Do you want to remove these characters in CSV file or in the table? If in CSV, then search and replace, if in a table, then use REPLACE() function and UPDATE command.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Tuesday, May 25, 2010 4:16 PM
    Moderator
  • You can use the REPLACE function:

    http://msdn.microsoft.com/en-us/library/ms186862.aspx

    Here are different techniques for cleanup:

    http://www.projectdmx.com/tsql/strcleanup.aspx


    Plamen Ratchev
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Tuesday, May 25, 2010 4:17 PM
    Moderator
  • If you have multiple special character the use the following

    Using a series of REPLACE() functions

    Here each REPLACE is used to change one unwanted character to a blank space or SPACE(0). The nesting of REPLACE function in recent version of the SQL Server product can go to hundreds of levels.

    SELECT REPLACE( REPLACE( REPLACE( REPLACE( @str, '!', '' ), '#', '' ), '$', '' ), '&', '' );

    If you have single special character the use the following

    Using a REPLACE() function

    Here each REPLACE is used to change one unwanted character to a blank space or SPACE(0). The nesting of REPLACE function in recent version of the SQL Server product can go to hundreds of levels.

    SELECT REPLACE(@str, '#', '' )

    • Edited by Hardi Patel Wednesday, May 26, 2010 11:11 AM further explanation
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 10:46 AM
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 10:57 AM
  • Use this function, It will Remove all the Special Character.

    DECLARE @str VARCHAR(25)
    SET @str = '(Har) DIK-patel123'
    WHILE PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ) > 0
              SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ), 1 ),''),'-',' ')
    SELECT @str

    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 11:24 AM
  • A few IF's.  If you are on 2005 or higher and if you are running and active SSIS instance, this would be really beneficial to performance increases by loading your csv with SSIS.  During the load a script component can be used to handle the manipulation of the strings per column.  Either that or in the least, a script task can handle the (pre)processing of the csv file to prepare it for a laod into SQL Server.

    Remember that it is always helpful for us if you specify the version and edition of SQL Server when asking questions.


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 12:00 PM
    Moderator

All replies

  • Do you want to remove these characters in CSV file or in the table? If in CSV, then search and replace, if in a table, then use REPLACE() function and UPDATE command.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Tuesday, May 25, 2010 4:16 PM
    Moderator
  • You can use the REPLACE function:

    http://msdn.microsoft.com/en-us/library/ms186862.aspx

    Here are different techniques for cleanup:

    http://www.projectdmx.com/tsql/strcleanup.aspx


    Plamen Ratchev
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Tuesday, May 25, 2010 4:17 PM
    Moderator
  • Thanks Naom & Palmen i have to do it in sql table itself

     

    thanks for your response, i will check it

     

    Best Regards

    asita

    Tuesday, May 25, 2010 4:35 PM
  • If you have multiple special character the use the following

    Using a series of REPLACE() functions

    Here each REPLACE is used to change one unwanted character to a blank space or SPACE(0). The nesting of REPLACE function in recent version of the SQL Server product can go to hundreds of levels.

    SELECT REPLACE( REPLACE( REPLACE( REPLACE( @str, '!', '' ), '#', '' ), '$', '' ), '&', '' );

    If you have single special character the use the following

    Using a REPLACE() function

    Here each REPLACE is used to change one unwanted character to a blank space or SPACE(0). The nesting of REPLACE function in recent version of the SQL Server product can go to hundreds of levels.

    SELECT REPLACE(@str, '#', '' )

    • Edited by Hardi Patel Wednesday, May 26, 2010 11:11 AM further explanation
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 10:46 AM
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 10:57 AM
  • Use this function, It will Remove all the Special Character.

    DECLARE @str VARCHAR(25)
    SET @str = '(Har) DIK-patel123'
    WHILE PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ) > 0
              SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ), 1 ),''),'-',' ')
    SELECT @str

    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 11:24 AM
  • /*********************************
    Removes any characters from
    @myString that do not meet the
    provided criteria.
    *********************************/
    CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
    RETURNS varchar(500) AS
    BEGIN

    While @myString like '%[^' + @validChars + ']%'
    Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

    Return @myString
    END
    Go

    Declare @testStr varchar(1000),
    @i int

    Set @i = 1
    while @i < 255
    Select
    @TestStr = isnull(@TestStr,'') + isnull(char(@i),''),
    @i = @i + 1

    Select @TestStr
    Select dbo.GetCharacters(@TestStr,'a-z')
    Select dbo.GetCharacters(@TestStr,'0-9')
    Select dbo.GetCharacters(@TestStr,'0-9a-z')
    Select dbo.GetCharacters(@TestStr,'02468bferlki')
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 11:56 AM
  • A few IF's.  If you are on 2005 or higher and if you are running and active SSIS instance, this would be really beneficial to performance increases by loading your csv with SSIS.  During the load a script component can be used to handle the manipulation of the strings per column.  Either that or in the least, a script task can handle the (pre)processing of the csv file to prepare it for a laod into SQL Server.

    Remember that it is always helpful for us if you specify the version and edition of SQL Server when asking questions.


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    • Marked as answer by KJian_ Monday, May 31, 2010 9:16 AM
    Wednesday, May 26, 2010 12:00 PM
    Moderator
  • /*********************************
    Removes any characters from
    @myString that do not meet the
    provided criteria.
    *********************************/
    CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
    RETURNS varchar(500) AS
    BEGIN

    While @myString like '%[^' + @validChars + ']%'
    Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

    Return @myString
    END
    Go

    Declare @testStr varchar(1000),
    @i int

    Set @i = 1
    while @i < 255
    Select
    @TestStr = isnull(@TestStr,'') + isnull(char(@i),''),
    @i = @i + 1

    Select @TestStr
    Select dbo.GetCharacters(@TestStr,'a-z')
    Select dbo.GetCharacters(@TestStr,'0-9')
    Select dbo.GetCharacters(@TestStr,'0-9a-z')
    Select dbo.GetCharacters(@TestStr,'02468bferlki')

    perfect soluction
    Wednesday, November 24, 2010 3:30 PM
  • absolutely perfect solution. good job.

    - Hal Diggs

    Wednesday, April 23, 2014 5:37 PM