Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    424
    Thanks
    8
    Thanked 6 Times in 6 Posts

    How would I do a REPLACE for SELECT * ??

    We've got some convoluted data in ramshackle tables, and I'm exporting it so we can actually use it and grow.
    The info is currently in MS SQL, and I will be going to MySQL.

    I need to SELECT * for these tables and have all fields REPLACE({field_value},'"',"''") ... or something.
    Some of these tables have about a hundred columns, and I need to replace all double-quotes (") with double single-quotes ('').

    I'm hoping there is a way to do this efficiently without having to type in all those REPLACE's manually, because that's not much of an option at all

    Pointers??
    Links??
    Advice??
    ~ Mo
    ...because it's dundant already.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,603
    Thanks
    80
    Thanked 4,633 Times in 4,595 Posts
    Yeah, you can do it with the help of sys.columns and sys.tables

    After connecting to SQL Server and choosing the database you will work with (which is usually just the DEFAULT DATABASE specified in the connection string), you can do:
    Code:
    SELECT C.* 
    FROM sys.columns AS C, sys.tables AS T
    WHERE C.objectid = T.objectid
    AND T.name = '...name of the table you want to work with...'
    More specifically, you will be interested in (most likely)
    Code:
    select c.column_id, c.name, t.name, c.max_length, c.precision, c.scale
    from sys.columns as c, sys.types as t, sys.tables as tbl
    where tbl.name = '...name of table...'
    and c.object_id = tbl.object_id
    and c.system_type_id = t.system_type_id
    order by column_id
    You might get some column_id's twice...I think that's because of how keys are recorded. Just pick the one that has the ordinary type name, not something like "sysname".

    I assume you want to convert ' to \' so you can dump the data from MySQL into a format usable for import into MySQL?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •