Remove non numerical characters from a string? 2

[christer99]

How do I remove non numerical characters from this select statement? I have all kinds of non numerical in this field, including ,+/=- etc

SELECT CONVERT(FLOAT, SUBSTRING(VEHINFO2, 10, 6)) AS VEHVAL
FROM AP3DBFB
WHERE (SUBSTRING(VEHINFO2, 10, 6) IS NOT NULL) AND (SUBSTRING(VEHINFO2, 10, 6) <> '')
ORDER BY SUBSTRING(VEHINFO2, 9, 7) DESC

 

[SQLSister]

A couple of things, do you need these permanently removed? Is the table large? if so and you can't permanently strip them then you will not want to do this just with a straight Select. It has to go character by character to find and strip the non-numbers which will be horribly slow if you have much data at all. This is a case where you might want to consider denormalizing the table by creating a field which stores just the numbers (populated by a trigger or as a calclualted field), then you won't need to do all this at the time you select the data.

At any rate you will have to write a loop of some kind to check each character to see if it is a number and then replace it with nothing if it is not. We do this sort of work with functions bearing in mind that functions are slow but there really is no fast way to do this. I fyou know some of the records wil contain only numbers it is best to skip the looping by checking using the isnumber function first.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[christer99]

I tried to use isnumber function but SQL 2000 is saying this function doesn't exist. Can you give me an example how to use it.

"isnumber is not a recognized function?

I can't and don't want to remove anything permanently, but the isnumber would actually fix my problem (if I can figure out why it is not working on my server. Again if you have an example how to use isnumber I would appreciate it).

 

[SQLSister]

I'm sorry i meant isnumeric

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLDenis]

the function is called isnumeric and it has a bug ;-)
it will return 1d2 as numeric

DECLARE @S VARCHAR(50)
SET @S = CHAR(9) --- @S NOW CONTAINS THE TAB CHARACTER
SELECT ISNUMERIC(@S), ISNUMERIC(CHAR(9)),ISNUMERIC('1D2'),ISNUMERIC('D')

see below how to do it correctly

CREATE TABLE #foo (Value VARCHAR(20))
INSERT INTO #foo
SELECT '1' UNION ALL
SELECT '3' UNION ALL
SELECT 'B' UNION ALL
SELECT '2' UNION ALL
SELECT '33.331' UNION ALL
SELECT 'adad1' UNION ALL
SELECT '1d2' UNION ALL
SELECT '^' UNION ALL
SELECT '17777.999'

--returns ^
SELECT * FROM #foo
WHERE Value NOT LIKE '%[a-z]%'

--returns 1d2
SELECT * FROM #foo
WHERE ISNUMERIC(Value) = 1

--returns correct result
SELECT * FROM #foo
WHERE Value NOT LIKE '%[a-z]%'
AND ISNUMERIC(Value) = 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/