-2

When the user enters input like $100, 10,000 or €100, or any such format, I need to not consider it as a numeric value for $100, 10,000 or €100. I try to use isnumeric, but it returns 1 for all these cases.

Help on this any one?

DECLARE @var varchar(100) 
SET @var = '$1000'

SELECT ISNUMERIC(@var)

SELECT 
    CASE 
       WHEN ISNUMERIC (@var) = 1 
          THEN CAST(@var AS numeric(36, 4)) 
          ELSE CAST('0' AS numeric(36,4))  
    END

enter image description here

DECLARE @var varchar(100) 
SET @var = '$1000'

SELECT ISNUMERIC(@var)

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rajendran S
  • 39
  • 1
  • 12
  • I assume there is some front-end application between user and database. Can you remove those currency characters there and make sure it's a real numeric value before the value gets to that database? – Hans Kesting Apr 02 '21 at 07:27
  • 3
    Does this answer your question? [SQL Server's ISNUMERIC function](https://stackoverflow.com/questions/32135878/sql-servers-isnumeric-function) – GSerg Apr 02 '21 at 07:28
  • 1
    What is your server version? – Denis Rubashkin Apr 02 '21 at 08:02
  • SQL Server's ISNUMERIC function – It work normal text and data but when include the $ % it not working – Rajendran S Apr 10 '21 at 07:20

4 Answers4

1

If you use MS SQL 2012 or higher you could use TRY_CONVERT and TRY_CAST functions:

DECLARE @var varchar(100);
SET @var = '$1000';

SELECT 
    ISNULL( TRY_CAST(@var AS numeric(36, 4)), 0 )

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Denis Rubashkin
  • 2,151
  • 1
  • 9
  • 14
1

As you're dealing with a string initially a couple of things you can try that might help.

Firstly, if your currency will always be a single character you can check if it's anything but a number and remove it before using the value:

declare @var varchar(100) 
set @var = '€1000'
if not(Ascii(Left(@var,1)) between 48 and 57)
    set @var=Replace(@var,Left(@var,1),'')
select @var

If you need to deal with currency symbols that are more than a single character you can use a combination of substring and patindex to get only the numeric digits. This is not particularly advanced but works fine assuming it's only ever monetary values:

declare @var varchar(100) 
set @var = 'CHF123,456.78'

set @var=Substring(@var, PatIndex('%[0-9]%', @var), Len(@var))
select @var
Stu
  • 30,392
  • 6
  • 14
  • 33
  • 1
    You have answered the question "How do I parse a number out of a string that may start with non-digit characters". The OP asked "How do I ignore a string that contains non-digit characters because it's invalid input for me". Hence, contrary to your statement, the answer absolutely does not address the question, and should be downvoted as irrelevant. The only answer currently present that addresses the question asked is https://stackoverflow.com/a/66916315/11683, but even that should not really have been upvoted because a duplicate had been suggested that contains the same solution. – GSerg Apr 03 '21 at 11:21
  • 2
    Hi @GSerg, thanks for clarifying - I don't disagree with your comment, I would simply say that the OP did not specifically say those words; like many questions on SO, as I'm sure you'll apreciate, there's often ambuguity and unclear intention and it seems I misinterpereted on this occaision, for which I apologise - had the question been simply "how to ignore if input contains any character other than numbers/digits" I would have absolutely offered a different suggestion. – Stu Apr 03 '21 at 11:33
  • Thanks for comments and clarification – Rajendran S May 14 '21 at 13:59
0

Microsoft SQL Server's ISNUMERIC function will return true for currency and some other symbols (like + and -). Your variable is still type varchar, however. See here for more info:

https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver15

Check out this SO post as well. The first answer provides a workaround to determine if a number is truly numeric:

SQL IsNumeric not working

Joseph
  • 865
  • 5
  • 20
0

You can use data type Money

MS Docs money and smallmoney (Transact-SQL)

declare @var varchar(100) SET @var='$1000'
select ISNUMERIC(@var)

SELECT case when isnumeric(@var)=1 then   CAST(@var AS money) else cast('0' as money)  end

1000.00

or TRY_PARSE

SELECT CAST(TRY_PARSE(@var AS money) AS numeric(36,4))
AlexK
  • 9,447
  • 2
  • 23
  • 32