Converting Data Type Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting Data Type Error

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-30 : 08:38:31

I have this query and I am getting an error message, I am not sure how to fix it. I thought it was the decimal place, but it is not. See below.

The error message I am getting is "Error converting data type varchar to numeric".

select
prin_diag ,
case when substring(prin_diag,1,1) in ('E','V') then
substring(prin_diag,1,1) + '' + cast(round(cast (substring(prin_diag,2,10) as decimal(10,2)),3) as varchar)
else cast (round(cast ( prin_diag as decimal(10,2)),2) as varchar)
end
from
dbo.CLAIMHEADERTEMP

group by prin_diag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 08:40:35

As varchar(what)?

E 12°55'05.63"
N 56°04'39.26"

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-30 : 08:42:04

I don't understand what you are asking?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 08:45:13

CAST AS VARCHAR (without size) can truncate your result unexpectedly.
Set a size for the cast.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 08:48:36

[code]SELECT prin_diag,
CASE
WHEN prin_diag LIKE '[EV]%' THEN SUBSTRING(prin_diag, 1, 1) + CAST(ROUND(CAST(SUBSTRING(prin_diag, 2, 10) AS DECIMAL(10,2)), 3) AS VARCHAR(12))
ELSE CAST(ROUND(CAST(prin_diag AS DECIMAL(10,2)), 2) AS VARCHAR(12))
END
FROM dbo.CLAIMHEADERTEMP
GROUP BY prin_diag[/code]

E 12°55'05.63"
N 56°04'39.26"

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-30 : 09:16:05

I am still getting an error. Do you think it has to do with (10,2)),3)

select
prin_diag ,
case when substring(prin_diag,1,1) in ('E','V') then
substring(prin_diag,1,1) + '' + cast(round(cast (substring(prin_diag,2,10) as decimal(10,2)),3) as varchar)
else cast (round(cast ( prin_diag as decimal(10,2)),2) as varchar (12))
end
from
dbo.CLAIMHEADERTEMP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:20:52

No need for extra round, since cast DECIMAL(10, 2) already have rounded your result.

SELECT		prin_diag,
		CASE
			WHEN prin_diag LIKE '[EV]%' THEN SUBSTRING(prin_diag, 1, 1) + CAST(CONVERT(DECIMAL(10,2), SUBSTRING(prin_diag, 2, 10)) AS VARCHAR(12))
			ELSE CAST(CONVERT(DECIMAL(10,2), prin_diag) AS VARCHAR(12))
		END
FROM		dbo.CLAIMHEADERTEMP 
GROUP BY	prin_diag

E 12°55'05.63"
N 56°04'39.26"

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-30 : 09:31:00

well, I just found out that the field that I am trying to convert has alpha in it as well. This field is a varchar 6. I think that is the reason I am getting the error. I have some date that says V701.12

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:34:30

So, what was the output from my suggested query?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:38:30

[code]DECLARE @Sample TABLE
(
Data VARCHAR(10)
)

INSERT @Sample
SELECT 'V701.12' UNION ALL
SELECT 'E666.81' UNION ALL
SELECT '123.45'

SELECT Data,
SUM(CONVERT(DECIMAL(10, 2), SUBSTRING(Data, PATINDEX('%[0-9]%', Data), 10)))
FROM @Sample
GROUP BY Data[/code]

E 12°55'05.63"
N 56°04'39.26"

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-30 : 09:41:17

I was still getting Error converting data type varchar to numeric".

The last statment you gave me worked. Thanks!

atulmar
Starting Member

7 Posts

Posted - 2008-09-30 : 11:46:29

Why dont you run your query with select, without doing this conversion to decimal or numeric.

This must be a data issue in your table.

Thanks
Atul

Thanks
Atul

atulmar
Starting Member

7 Posts

Posted - 2008-09-30 : 11:48:34

Additionally you check for ISNUMERIC function, if your data is convertible. Otherwise deselect the data that is not convertible by filtering on ISNUMERIC(<Value>) = 0

Thanks
Atul

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-09-30 : 15:39:24

This does work, but it is taking my V and E out of the results. Has V701.12 then result is 701.12

DECLARE @Sample TABLE
(
Data VARCHAR(10)
)

INSERT @Sample
SELECT 'V701.12' UNION ALL
SELECT 'E666.81' UNION ALL
SELECT '123.45'

SELECT Data,
SUM(CONVERT(DECIMAL(10, 2), SUBSTRING(Data, PATINDEX('%[0-9]%', Data), 10)))
FROM @Sample
GROUP BY Data

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 15:54:30

Maybe you should tell us the purpose of the query, beacuse the query makes no sense.
You can aggregate strings with MIN and MAX, not SUM.

Is this what you want?

DECLARE	@Sample TABLE
	(
		Data VARCHAR(10)
	)

INSERT	@Sample
SELECT	'V701.12' UNION ALL
SELECT	'E666.81' UNION ALL
SELECT	'123.45'

SELECT		Data,
		LTRIM(STUFF(Data, PATINDEX('%[0-9]%', Data), 0, ' '))
FROM		@Sample

If it is not, please read, understand and follow the advice given here
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources