Member Avatar for normmy

Hi all hopefully I can explain my problem correctly, this is my first post on DaniWeb.

I'm currently working on a legacy medical research database that uses a Varchar field to store a decimal number, this field can also be used to store text entered by a researcher on the paper form used to populate the database.

I'm currently producing reporting views on this database and I need to find a way to detect alpha characters in the variable and return null if a decimal number is not the only thing in the string.

I need to use this value to calculate minimum, maximum and average values by casting or converting it.

the following example works for null values and empty strings, I just need to test for any alpha chars before i allow cast to change the value to a decimal.

select case when (HbA1c_perc is NULL) or (HbA1c_perc = '') then 
	NULL 
else 
	cast(HbA1c_perc as decimal(8,2)) 
end as HbA1c_perc
from clinical

thanks in advance for any help you can give me.

:)

  • 2 Contributors
  • 3 Replies
  • 187 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by sknake

Dani AI

Generated

Short summary: ’s HbA1c_perc column contains either a decimal stored as text or free text entered on the paper form. The goal is to treat only pure numeric strings as decimals (so aggregates work) and return NULL for anything that contains letters or otherwise isn’t a single valid decimal.

A quick filter many reach for is ’s suggestion to use ISNUMERIC. That’s convenient, but ISNUMERIC is permissive by design: it returns 1 when the expression can be converted to any numeric type (including money), so plus/minus, currency symbols, commas, even a lone dot can return 1. That behaviour means ISNUMERIC alone can let through values that will still fail a CAST to decimal or produce misleading results. (learn.microsoft.com)

Safer (and simplest) when available: use the TRY_ family (SQL Server 2012+). TRY_CONVERT/TRY_CAST attempt the conversion and return NULL on failure, so they naturally give the desired NULL for non‑numeric or out‑of‑range values and play nicely with MIN/MAX/AVG. For example, converting to decimal(8,2) will return NULL when the string can’t be interpreted as that numeric form or doesn’t fit the precision. (learn.microsoft.com)

If stuck on an older SQL Server, use a defensive pattern check before casting: trim the string, ensure it contains only digits and at most one decimal point, strip known thousand separators or non‑breaking spaces, then CAST. A common lightweight test is the NOT LIKE pattern that rejects any character outside 0–9 and dot; add a dot-count check to avoid multiple decimals. This is a pragmatic fallback but has limits (locale decimal commas, scientific notation, leading signs, and edge cases like non‑breaking spaces), so consider a CLR/regex validation or, better, normalizing values at ingest if possible. (stackoverflow.com)

Recommended Answers

All 3 Replies

Member Avatar for sknake

IsNumeric() should do the trick:

IF OBJECT_ID('tempdb..#Test', 'U') IS NOT NULL DROP TABLE #Test
Create Table #Test
(
  Value varchar(10)
)

Insert Into #Test (Value) Values ('a')
Insert Into #Test (Value) Values ('1')
Insert Into #Test (Value) Values ('ab')
Insert Into #Test (Value) Values ('a2')
Insert Into #Test (Value) Values ('azz')
Insert Into #Test (Value) Values ('a2.2')
Insert Into #Test (Value) Values ('65.2')
Insert Into #Test (Value) Values ('99.1')
Insert Into #Test (Value) Values ('65.2a')

Select Min(Cast(Value as money)) As [Min], Max(Cast(Value as money)) As [Max], Avg(Cast(Value as money)) as [Avg]
From #Test
Where IsNumeric(Value) = 1
Edited by sknake because: n/a
Member Avatar for normmy

Thanks for that!

Member Avatar for sknake

You're welcome

Please mark this thread as solved if you found an answer to your question and good luck!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.