Professional Web Applications Themes

Replace character in SELECT results - Microsoft SQL / MS SQL Server

Hi, I have a SELECT statement of multiple columns from joined tables. One of the columns is an address field. It is one field that contains the name, street address, city, state, zip each seperated by a linefeed (Oa) character. I am trying to print the results of the SELECT statement and this field is printing on one line. Is there a function (CASE or something else) that I can include in the SELECT statement that would in the results of SELECT replace the linefeed with a carriage return/ linefeed combination so the results would print on multiple lines as ...

  1. #1

    Default Replace character in SELECT results

    Hi,

    I have a SELECT statement of multiple columns from joined tables. One of
    the columns is an address field. It is one field that contains the name,
    street address, city, state, zip each seperated by a linefeed (Oa)
    character. I am trying to print the results of the SELECT statement and
    this field is printing on one line. Is there a function (CASE or
    something else) that I can include in the SELECT statement that would in
    the results of SELECT replace the linefeed with a carriage return/
    linefeed combination so the results would print on multiple lines as the
    address should?

    If so, I would greatly appreciate it if someone could give an example of
    how to do it.

    Thanks,

    David
    David Guest

  2. #2

    Default Re: Replace character in SELECT results

    > character. I am trying to print the results of the SELECT statement and
    > this field is printing on one line.
    How to "fix" this really depends... in what application? Query yzer, a
    browser, a VB MsgBox, ...?


    Aaron Bertrand [MVP] Guest

  3. #3

    Default Re: Replace character in SELECT results

    Hi Arron,

    I am using some shareware software I am trying out. It is used to design
    a form or label layout that will print using either fields returned from
    a SELECT statement using an Access\SQL DB or static text fields
    pre-defined in the layout. The only options I have to manipulate the
    data returned from the SQL statement is either some function included in
    the SQL statement itself or a stripped down VBScript included in the
    software. I thought I would try the SQL statement since I know a little
    more about SQL than VBScript. How can I reference a DB field by name in
    a VBScript and replace a linefeed with a carriage return? The DB field
    is printing on one line rather than multiple lines as it should. I need
    to use either a SQL statement or VBScript to get the field to show
    up/print in the design as it would in Wordpad rather than Notepad. I
    know if I paste the results of SELECT to Notepad it shows up as 1 line.
    If I paste the results of SELECT in Wordpad it shows up as multiple
    lines.

    Thanks,

    David


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    David V Guest

  4. #4

    Default Re: Replace character in SELECT results

    > more about SQL than VBScript. How can I reference a DB field by name in
    > a VBScript and replace a linefeed with a carriage return?
    val = rs("column")
    val = replace(val, chr(10), chr(13))

    (Or maybe I have those backward.)

    You really should be aiming at having a full vbCrLf pair (or getting the
    data into the database correctly in the first place).



    Aaron Bertrand - MVP Guest

Similar Threads

  1. #40035 [NEW]: [RFE] get_html_translation_table() add option to select character set
    By leonard-php-bugs at ottolander dot nl in forum PHP Bugs
    Replies: 1
    Last Post: January 5th, 07:09 PM
  2. Replies: 3
    Last Post: March 2nd, 09:24 PM
  3. Find/Replace Results Panel
    By Victor Corey in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 23rd, 11:49 PM
  4. DB2 Select - how to return a range of results
    By Igor Pool in forum IBM DB2
    Replies: 2
    Last Post: July 15th, 01:09 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