July 25, 2008 at 6:42 am
When I convert Tid it works for everything else except for RowId one?
I tried Bigin and it still doesn't work? How do I work around this ?
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test
GO
CREATE TABLE #Test(RowId Int Identity(1,1),Tid nvarchar(510))
INSERT #Test VALUES('1.7860999999999999E+32')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('452647')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.65248e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.65248e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('0')
INSERT #Test VALUES('0')
INSERT #Test VALUES('0')
INSERT #Test VALUES('0')
INSERT #Test VALUES('609096')
INSERT #Test VALUES('76524801')
INSERT #Test VALUES('3.99001e+006')
INSERT #Test VALUES('6.81132e+007')
INSERT #Test VALUES('76524801')
INSERT #Test VALUES('76524805')
INSERT #Test VALUES('12925')
INSERT #Test VALUES('32211622')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('22063')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('S11190')
INSERT #Test VALUES('0000K420')
INSERT #Test VALUES('28151006')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('7.4634e+007')
INSERT #Test VALUES('70691585')
INSERT #Test VALUES('0')
INSERT #Test VALUES('NULL')
INSERT #Test VALUES('0')
INSERT #Test VALUES('BAM6')
INSERT #Test VALUES('BAM6')
INSERT #Test VALUES('BAM6')
INSERT #Test VALUES('BAM6')
INSERT #Test VALUES('S10300')
INSERT #Test VALUES('74633985')
INSERT #Test VALUES('S10001')
INSERT #Test VALUES('76524801')
INSERT #Test VALUES('800013')
SELECT
RowId,
Tid ,
CASE
WHEN ISNUMERIC(Tid) = 1
THEN CAST(CONVERT(NVARCHAR,Tid) AS FLOAT)
ELSE Tid END AS TermID
FROM #Test
WHERE Tid LIKE '%[.,+]%'
July 25, 2008 at 6:56 am
Well, 1.7861E+32 is slighty less than 2*10^32, which is approximately 2^108, which is way more than 2^63-1, the highest positive number that Bigint can represent. If you want numbers this big, I suggest that you stick to floating point or decimal numeric types, that is after all what they were made for.
Types that you can use are : Numeric(p, s) with p greater than 32, Float, and Real.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply