Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2004
    Posts
    3

    Unanswered: Jioning Text fields together in SQL

    Hi, I'm sure that this is probably a really easy question to most of you, but I seem to be spending ages on it as I am new to SQL. how do I add the carraige return char between joining two text fields ie.

    a_blah.blah+CHAR(13)+b_blah.blah


    Thanks for your help.

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    That seems right, unless that is part of your result set, then you'll need an "AS" clause:

    (table1.value1 + CHAR(13) + table2.value2) AS CombinedValue

    Depending upon the datatype of value1 an value2, you need to CONVERT or CAST first.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    May 2004
    Posts
    3
    Thanks for the reply....

    That is pretty much what I'm doing with the AS command, but I get an error when I try to run the query... can't find prog.char(13)????

    I'm confused.

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    This is straight out of the BOL:
    Code:
    USE Northwind
    SELECT FirstName + ' ' + LastName, + CHAR(13) + Address, 
       + CHAR(13) + City, + Region 
    FROM Employees
    WHERE EmployeeID = 1
    When I run it, it works fine. Can you use CHAR for a normal alphabetic character? CHAR(65) = A
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    May 2004
    Posts
    3
    It didn't work on mine???? This is the actual code that I'm trying to run... I do appreciate your help.

    SELECT a_psupp.comp_no, a_psupp.comp_name AS 'Supplier Code',

    a_psupp.add1 + CHAR(13) + a_psupp.add2 + CHAR(13) +
    a_psupp.add3 + CHAR(13) + a_psupp.add4 + CHAR(13) +
    a_psupp.add5 AS 'Ordering Address',

    a_psupp.tel AS 'Telephone Number',
    a_psupp.fax AS 'Fax Number',
    a_psupp.comm1+" "+a_psupp.comm2 AS 'Supplier Notes'

    FROM a_psupp a_psupp

    ORDER BY a_psupp.comp_no


    When this runs i get the error Can't Find Char.PRG

    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Seppuku
    This is straight out of the BOL:
    Code:
    USE Northwind
    SELECT FirstName + ' ' + LastName, + CHAR(13) + Address, 
       + CHAR(13) + City, + Region 
    FROM Employees
    WHERE EmployeeID = 1
    if that's straight out of BOL, it has syntax errors!!!

    it is followed by:
    Here is the result set:
    Code:
    Nancy Davolio
    507 - 20th Ave. E.
    Apt. 2A
    Seattle            WA
    Note: In this record, the data in the Address column also contains a control character.
    that's gotta be the stupidest example i've seen in a long time

    "WA" stored in the Region column? i don't have northwind, so i can't check

    and where did the spacing come from?

    tsk, where was basic QA when this was written...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote Originally Posted by r937
    if that's straight out of BOL, it has syntax errors!!!

    tsk, where was basic QA when this was written...
    I thought the same, but I didn't want to doctor the BOL if I was going to quote it. It still does run, but I agree.


    a_psupp.comm1+" "+a_psupp.comm2 AS 'Supplier Notes'
    cfeltham, are those double quotes in your query? Replace them with single quotes and retry.
    That which does not kill me postpones the inevitable.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The double quotes might be hosing things up. On a side note, you should check for NULL values when concatenating text strings, as the concatenation of a NULL value with a string value normally returns NULL (Unless you specifically change the CONCAT_NULL_YIELDS_NULL setting).

    SELECT a_psupp.comp_no,
    a_psupp.comp_name AS 'Supplier Code',
    isnull(a_psupp.add1, '') + CHAR(13)
    + isnull(a_psupp.add2, '') + CHAR(13)
    + isnull(a_psupp.add3, '') + CHAR(13)
    + isnull(a_psupp.add4, '') + CHAR(13)
    + isnull(a_psupp.add5, '') AS 'Ordering Address',
    a_psupp.tel AS 'Telephone Number',
    a_psupp.fax AS 'Fax Number',
    isnull(a_psupp.comm1 + ' ', '') + isnull(a_psupp.comm2, '') AS 'Supplier Notes'
    FROM a_psupp
    ORDER BY a_psupp.comp_no
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Seppuku
    I thought the same, but I didn't want to doctor the BOL if I was going to quote it.
    sir/madam, i quite admire your standards

    It still does run...
    wtf? i don't believe it

    omg, you are right, you can actually start an expression with a concatenation operator
    Code:
    create table concatstrings
    ( id smallint not null identity primary key
    , foo  varchar(9)
    , bar  varchar(9)
    , qux  carchar(9)
    )
    
    insert into timediffs ( foo,bar,qux ) values ( 'the','three','stooges' )
    insert into timediffs ( foo,bar,qux ) values ( 'curly','larry','moe' )
    
    select foo + ' ' + bar
         , + CHAR(13) + qux
         , + id
      from concatstrings
      
    the three	
    stooges	1
    curly larry	
    moe	2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote Originally Posted by r937
    sir/madam, i quite admire your standards

    wtf? i don't believe it

    omg, you are right, you can actually start an expression with a concatenation operator
    sir will be fine, and thank you I'm just a humble programmer, not a DBA, so I didn't want to insert my foot in my mouth and point out the possible error if there was a reason for it that I was unaware. Stranger things have happened...

    INSERT INTO Mouth (LeftFoot, RightFoot) VALUES (1,1)

    That which does not kill me postpones the inevitable.

  11. #11
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    way too freaky
    fyi
    try the ascii() function
    it's the opposite of char()

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Ruprect
    try the ascii() function
    it's the opposite of char()
    So are you suggesting that he insert his ASCII into his mouth ?!?!

    -PatP

  13. #13
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    you betcha, sport.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Eeewwww!

    -PatP

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ...and the checks in the mail....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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