convert problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert problem

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-01 : 09:35:40

Hi

I use this piece...

CASE WHEN ISNUMERIC(MyBooksPrice) = 1 THEN CONVERT(DECIMAL(18, 2), MyBooksPrice) ELSE 0.0 END AS MyBooksPrice

But if the columnn is empty, this fails, how do I prevent that and if it is empty return 0.0?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 09:38:01

CASE WHEN ISNUMERIC(MyBooksPrice) = 1 THEN CONVERT(DECIMAL(18, 2), COALESCE(MyBooksPrice,0.0)) ELSE 0.0 END AS MyBooksPrice

Madhivanan

Failing to plan is Planning to fail

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-01 : 10:05:10

Hi

I still get a "Error converting data type nvarchar to numeric." even after I added this part.. Is there anything else I can do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-01 : 10:24:40

I don't think you have empty records. Not with ASCII 32 anyway.
But... ISNUMERIC(CHAR(160)) will yield 1 as results. CHAR(160) is hard space.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-01 : 10:27:31

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-01 : 10:29:30

Hi Peso

When I run that I do get 1 as result, so what do I need to change in order to fix this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-01 : 10:33:27

It depends on how the CHAR(160) is used, if at all!

Run

SELECT COUNT(*) FROM Table1 WHERE MyBooksPrice LIKE '%' + CHAR(160) + '%'

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-01 : 10:40:44

I get 0 as result....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-01 : 11:12:44

Try this

SELECT	DISTINCT
	MyBooksPrice
FROM	Table1
WHERE	MyBooksPrice LIKE '%[^0123456789.£$]%'

It will give you all distinct records, where any of the characters are not "valid".

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-01 : 13:13:30

I get 256 rows like this...

17.8
83.47
'Note that this row is empty and the one that cause trouble
135.5
70,15
147.2
76,45

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 02:07:22

[code]DECLARE @Sample TABLE
(
MyBookPrice NVARCHAR(20)
)

INSERT @Sample
SELECT '' UNION ALL
SELECT ' ' UNION ALL
SELECT CHAR(160) UNION ALL
SELECT '1d4' UNION ALL
SELECT '1e-1' UNION ALL
SELECT '$99.95' UNION ALL
SELECT '£45.67'

SELECT s.MyBookPrice,
g.x AS thePosition,
SUBSTRING(s.MyBookPrice, g.x, 1) AS theChar,
ASCII(SUBSTRING(s.MyBookPrice, g.x, 1)) AS theASCII
FROM @Sample AS s
CROSS APPLY (
SELECT v.Number
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.Number BETWEEN 1 AND DATALENGTH(s.MyBookPrice)
) AS g(x)
WHERE SUBSTRING(s.MyBookPrice, g.x, 1) LIKE '%[^0123456789.£$]%'[/code]

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-02 : 02:20:40

Sorry, but I don't quite follow you... what should I do after running this code?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 02:39:47

With this code

SELECT		s.MyBookPrice,
		g.x AS thePosition,
		SUBSTRING(s.MyBookPrice, g.x, 1) AS theChar,
		ASCII(SUBSTRING(s.MyBookPrice, g.x, 1)) AS theASCII
FROM		YourTableNameHere AS s
CROSS APPLY	(
			SELECT		v.Number
			FROM		master..spt_values AS v
			WHERE		v.Type = 'P'
					AND v.Number BETWEEN 1 AND DATALENGTH(s.MyBookPrice)
		) AS g(x)
WHERE		SUBSTRING(s.MyBookPrice, g.x, 1) LIKE '%[^0123456789.£$]%'

you can see exactly for each and one record which character at which position that does not qualify as a numeric character.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 02:40:44

You will also get the ASCII value for the character that does not qualify as a numeric character.
Post back your result and then you can decide which approach to take to remedy this situation.

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-02 : 02:56:44

All I get is x number of rows with theChar = ,
and theASCII = 44

But I do not get the row which cause trouble...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 03:11:51

You only have theASCII = 44 ?
No other records at all?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 03:18:00

[code]DECLARE @Sample TABLE
(
MyBookPrice NVARCHAR(20)
)

INSERT @Sample
SELECT '' UNION ALL
SELECT NULL UNION ALL
SELECT ' ' UNION ALL
SELECT CHAR(160) UNION ALL
SELECT '1d4' UNION ALL
SELECT '1,4' UNION ALL
SELECT '1e-1' UNION ALL
SELECT '$99.95' UNION ALL
SELECT '£45.67'

SELECT MyBookPrice,
CASE
WHEN ISNUMERIC(MyBookPrice2) = 1 THEN CONVERT(DECIMAL(18, 2), CAST(MyBookPrice2 AS FLOAT))
ELSE 0.0
END
FROM (
SELECT MyBookPrice,
NULLIF(REPLACE(REPLACE(REPLACE(REPLACE(MyBookPrice, ',', '.'), CHAR(160), ''), '$', ''), '£', ''), '') AS MyBookPrice2
FROM @Sample
) AS d[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 03:19:02

This is a slimmed version of what I think you need.

DECLARE	@Sample TABLE
	(
		MyBookPrice NVARCHAR(20)
	)

INSERT	@Sample
SELECT	'' UNION ALL
SELECT	NULL UNION ALL
SELECT	' ' UNION ALL
SELECT	'1,4' UNION ALL
SELECT	'99.95'

SELECT	MyBookPrice,
	CASE
		WHEN ISNUMERIC(MyBookPrice2) = 1 THEN CONVERT(DECIMAL(18, 2), CAST(MyBookPrice2 AS FLOAT))
		ELSE 0.0
	END
FROM	(
		SELECT	MyBookPrice,
			NULLIF(REPLACE(MyBookPrice, ',', '.'), '') AS MyBookPrice2
		FROM	@Sample
	) AS d

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-02 : 03:29:02

If I use this piece...

NULLIF(REPLACE(REPLACE(REPLACE(REPLACE(MyBookPrice, ',', '.'), CHAR(160), ''), '$', ''), '£', ''), '') AS MyBookPrice2

I get NULL where the price is that cause trouble. I can then replace NULL with 0.0 if I need to and that should take care of the problem, Thanks a lot for all help! I really appreciate it!

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-02 : 03:51:44

By the way, is it possible to place all that logic (that you last posted) on one row?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 04:17:59

[code]SELECT MyBookPrice,
CASE
WHEN ISNUMERIC(NULLIF(REPLACE(MyBookPrice, ',', '.'), '') ) = 1 THEN CONVERT(DECIMAL(18, 2), CAST(NULLIF(REPLACE(MyBookPrice, ',', '.'), '') AS FLOAT))
ELSE 0.0
END
FROM @Sample[/code]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources