Professional Web Applications Themes

Remove carriage returns using SQL, UDB 7 - IBM DB2

I have a varchar 254 field that has embedded carriage returns. I thought I could use "select * from x where posstr(myfield,char(13)) > 0" to find rows with this in the field. However this never returns any matches. I also tried "select * from x where posstr(myfield,char(13)||char(10))  If I pull the data out into a file I can see the "^M" in the file. Anyone have any ideas? I would like to do this via SQL instead of extracting the data and correcting it....

  1. #1

    Default Remove carriage returns using SQL, UDB 7

    I have a varchar 254 field that has embedded carriage returns. I
    thought I could use "select * from x where posstr(myfield,char(13)) >
    0" to find rows with this in the field.

    However this never returns any matches.

    I also tried "select * from x where posstr(myfield,char(13)||char(10)) 

    If I pull the data out into a file I can see the "^M" in the file.

    Anyone have any ideas? I would like to do this via SQL instead of
    extracting the data and correcting it.
    mark Guest

  2. #2

    Default Re: Remove carriage returns using SQL, UDB 7

    How about
    db2 "select hex(myfield) from x where posstr(myfield, char(x'0A')) > 0"?

    Sherman

    "mark" <com> wrote in message
    news:google.com... 
    >
    > If I pull the data out into a file I can see the "^M" in the file.
    >
    > Anyone have any ideas? I would like to do this via SQL instead of
    > extracting the data and correcting it.[/ref]


    Sherman Guest

  3. #3

    Default Re: Remove carriage returns using SQL, UDB 7

    com (mark) wrote in message news:<google.com>... 
    >
    > If I pull the data out into a file I can see the "^M" in the file.
    >
    > Anyone have any ideas? I would like to do this via SQL instead of
    > extracting the data and correcting it.[/ref]

    Wrong function - use CHR instead of CHAR. If you want you can replace
    all the carriage returns with a string of your choice using the
    REPLACE function, e.g. to replace with a space:

    SELECT REPLACE(myfield, CHR(13), ' ') FROM x;


    Jeremy Rickard
    Jeremy Guest

Similar Threads

  1. Removing carriage returns...
    By JakeFlynn in forum Macromedia ColdFusion
    Replies: 3
    Last Post: April 5th, 08:54 PM
  2. replacing carriage returns in file
    By the real rob rone in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 17th, 05:35 PM
  3. XML, carriage returns and special characters.
    By Johnny Walker in forum Macromedia Flash
    Replies: 6
    Last Post: January 3rd, 03:00 AM
  4. replacing carriage returns?
    By yawnmoth in forum PHP Development
    Replies: 1
    Last Post: November 1st, 11:57 AM
  5. Extra carriage returns - why?
    By Bob Walton in forum PERL Miscellaneous
    Replies: 3
    Last Post: September 10th, 11:35 AM

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