Professional Web Applications Themes

need help with string manipulation - Microsoft SQL / MS SQL Server

i'm sure this has been ask a million time but don't seem to find it any where anyway... is there a way to change a caracter in a string in all rows of the db ex: changing all 'd' for all 'u' or all '#' for '&' etc thankx all...

  1. #1

    Default need help with string manipulation

    i'm sure this has been ask a million time but don't seem
    to find it any where anyway...

    is there a way to change a caracter in a string in all
    rows of the db ex:

    changing all 'd' for all 'u' or all '#' for '&' etc

    thankx all
    john Guest

  2. #2

    Default Re: need help with string manipulation

    John,

    Yes - and update query using replace()

    Regards
    AJ

    "john" <com> wrote in message news:03aa01c36abb$55357060$gbl... 


    Andrew Guest

  3. #3

    Default Re: need help with string manipulation

    AJ can you please give me example i don't seem to get this
    to work

    thankx
     
    news:03aa01c36abb$55357060$gbl... 
    >
    >
    >.
    >[/ref]
    john Guest

  4. #4

    Default Re: need help with string manipulation

    Hello John !

    Try this:

    DECLARE NAME Varchar(50)
    SET Name='Anna'

    Select Name
    Select Replace(Name,'a','o')

    --------
    Anna

    (1 row(s) affected)

    ----------------

    onno

    (1 row(s) affected)

    HTH, Jens Süßmeyer.


    Jens Guest

  5. #5

    Default Re: need help with string manipulation

    hey Jens,
    ya i have that working fine but i have to do that in they
    entire db all rows of the db must be check and cleaned

    any idea greatly appriciated
     
    john Guest

  6. #6

    Default Re: need help with string manipulation


    Then you will have to go through each row of every table in the database
    (Name of columns located in SYSCOLUMNS) and change the content. But beare of
    the PK and FK columns.

    If you need any further help to fo through the tables jut write it here.

    HTH, Jens Süßmeyer.




    Jens Guest

  7. #7

    Default Re: need help with string manipulation

    John,

    You might like to try something along these lines:

    declare Table varchar(200)
    declare Column varchar(200)

    declare crsTables
    cursor for
    select table_name, column_name
    from information_schema.columns
    where data_type in ('char', 'varchar', 'nchar', 'nvarchar')
    and table_name in
    ( select table_name
    from information_schema.tables
    where table_type = 'BASE TABLE'
    )

    open crsTables
    fetch next from crsTables
    into Table, Column

    while fetch_status = 0
    begin
    print 'Update [' + Table + '] set [' + Column + '] = replace( [' + Column + '], ''d'', ''u'')'
    fetch next from crsTables
    into Table, Column
    end

    close crsTables
    deallocate crsTables

    Cuting and pasting the resulting code into Query yser.

    BUT Run at your own risk ! It being such a "large" thing to do to a database backups would be essential

    And as Jens Süßmeyer states - you will have problems with foreign and primary keys,
    triggers, constraints, ...

    Regards
    AJ
     


    Andrew Guest

Similar Threads

  1. Classic ASP String Manipulation - NOT .net
    By James in forum ASP.NET Web Services
    Replies: 6
    Last Post: February 16th, 01:17 PM
  2. Need some help with string manipulation
    By Ramesh in forum Linux / Unix Administration
    Replies: 4
    Last Post: December 5th, 07:11 PM
  3. String Manipulation - Easiest Way?
    By Nanwedar in forum Macromedia ColdFusion
    Replies: 5
    Last Post: February 24th, 04:01 PM
  4. Another String Manipulation Question
    By Bill in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: February 21st, 03:31 AM
  5. String manipulation
    By Jerry Preston in forum PERL Beginners
    Replies: 2
    Last Post: January 19th, 01:27 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139