Post reply

Problem querying column containing an asterisk

  • December 20, 2012 at 11:00 am

    #393822

    Hi I have a table with a column which is defined as a varchar that contains asterisks. If I query that column I get the expected results,

    select col from tableA

    e.g. 1308064*8

    1522323*11

    However, if I select the same column using a query with a join condition I get the following output:

    Select col from tableA A

    left join tableB B

    on a.key = b.key

    8.56E+17

    8.61E+18

    In fact I get the same output if I include a second column in the first query, e.g

    Select key.col

    from tableA

    I'm running against a Sql2005 SP3 database.

    Anyone got any ideas what is happening here or how to ensure I get the actual varchar value?

  • December 20, 2012 at 11:15 am

    #1570632

    garth i could not get a result to work the way you said; we would need something equivilent to this to test with:

    With TableA (,col)

    AS

    (SELECT 1,'1308064*8' UNION ALL

    SELECT 2, '1522323*11'

    ),

    TableB (,col)

    AS

    (SELECT 1,'Some Stuff' UNION ALL

    SELECT 2, 'Other Stuff'

    )

    Select A.col ,*

    from tableA A

    left join tableB B

    on a. = b.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 20, 2012 at 11:27 am

    #1570635

    How are you executing the query? Wouldn't be directly into Excel by any chance, would it?

    And then again, I might be wrong ...
    David Webb

  • December 20, 2012 at 3:15 pm

    #1570666

    Hi David, I wasn't using Excel just SSMS.

  • December 20, 2012 at 3:40 pm

    #1570670

    As Lowell suggested, posting the table definitions and some sample data in the form of insert statements would help. Can you replicate this behavior in other tables or is it just this particular column? Is this a table or a view? Can you post some of the varchar values and the scientific notation values that correspond to them?

    And then again, I might be wrong ...
    David Webb

  • December 21, 2012 at 2:37 am

    #1570805

    Apologies to all, this was not a problem after all but 2 rogue rows in the data! I'd been looking at the data that long, that I'd confinced myslef there was something wrong going on.

    Thanks for all the replies and sorry for wasting your time.

    Merry Christmas to all

    Gareth

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply