Post reply

Replace commas from all columns in a Table (Just one table)

  • August 8, 2016 at 6:58 pm

    #400542

    Looking to do a quick find a replace on all columns in a table and not all the tables.

    Replace comma with colon or *. Need to data cleanse, it is breaking my csv import/export..

  • August 8, 2016 at 8:34 pm

    #1894452

    the data is the data. don't modify the data, modify the query that is exporting to csv.

    if you use SSIS, you can modify it to export everything with double quotes, so the commas in the fields are maintained, and also don't break anything when people open it.

    you could modify the query to replace the offending characters too:

    select REPLACE(FullName,',','') As FullName.....

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 8, 2016 at 9:04 pm

    #1894453

    Hi,

    I'm using SSIS, but I'm also importing a csv file that the users prepare and then do a automation to export a much reduced, automatically functionally allocated file after joining with the database.

    I did try to change the delimiter to semicolon to import and export via Control Panel, but quickly realized everyone needs to follow this standard or Do a text to columns after export and change in .txt before upload, which could lead to more errors because it may replace the column , with ; or " and then the column will shift anyway.

    Agree the main data shouldn't change in ideal scenarios, but I'm cleansing it to replace it with colon or semicolon and the column delimiter can be remain comma in that case. Was aware of the replace function by individual column, if there something that will replace the commas in all columns in a table in one go ?

    Thanks,

    V

  • August 8, 2016 at 10:20 pm

    #1894458

    You can use INFORMATION_SCHEMA.COLUMNS or sys.columns to dynamically build a T-SQL statement, assign that statement to an @variable, and sp_executesql @variable

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply