SQL Server Performance Forum – Threads Archive
Numeric Check
How can I easily check a character field to make sure it’s all numeric before converting it? I need to know the exact line number of the problem record(s).Did you try: IsNumeric ? More information in BOL.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
Isnumeric is not always reliable [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Check what you want <br /><pre id="code"><font face="courier" size="2" id="code"><br />select * from<br />(<br />select ‘234’ as data union all<br />select ’52e34′ union all<br />select ‘8.3566’ union all<br />select ‘52.900’ <br />) T <br />where isnumeric(data)=1<br /><br /><br />select * from<br />(<br />select ‘234’ as data union all<br />select ’52e34′ union all<br />select ‘8.3566’ union all<br />select ‘52.900’ <br />) T <br />where data not like ‘%[a-z]%'<br /></font id="code"></pre id="code"><br />Also read this fully<br /<a target="_blank" href=http://aspfaq.com/show.asp?id=2390>http://aspfaq.com/show.asp?id=2390</a><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
http://www.sql-server-performance.com/q&a127.asp Another way would be to use PATINDEX
SELECT
PATINDEX(‘%[^0-9]%’, ‘€+,.’)
, PATINDEX(‘%[^0-9]%’, ‘1d2’)
, PATINDEX(‘%[^0-9]%’, ‘123,45.’)
, PATINDEX(‘%[^0-9]%’, ‘12345’)
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Well. Then other approach would be
select * from
(
select ‘234’ as data union all
select ’52e34′ union all
select ‘8.3566’ union all
select ‘52.900’
) T
where PATINDEX(‘%[^0-9.]%’, data)=0 Madhivanan Failing to plan is Planning to fail
]]>