Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem querying column containing an asterisk Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 11:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:13 AM
Points: 15, Visits: 397
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?
Post #1399071
Posted Thursday, December 20, 2012 11:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:17 PM
Points: 12,881, Visits: 31,820
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 ([key],col)
AS
(SELECT 1,'1308064*8' UNION ALL
SELECT 2, '1522323*11'
),
TableB ([key],col)
AS
(SELECT 1,'Some Stuff' UNION ALL
SELECT 2, 'Other Stuff'
)
Select A.col ,*
from tableA A
left join tableB B
on a.[key] = b.[key]





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1399077
Posted Thursday, December 20, 2012 11:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:55 PM
Points: 896, Visits: 7,087
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
Post #1399080
Posted Thursday, December 20, 2012 3:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:13 AM
Points: 15, Visits: 397
Hi David, I wasn't using Excel just SSMS.
Post #1399121
Posted Thursday, December 20, 2012 3:40 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:55 PM
Points: 896, Visits: 7,087
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
Post #1399125
Posted Friday, December 21, 2012 2:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:13 AM
Points: 15, Visits: 397
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
Post #1399287
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse