Judge whether the string is a number (test version)

Recently, I have participated in several interviews and made many interview questions for the reason I am looking for a job. The most important thing I remember is to judge whether the string is a number, at first, I wrote try... the catch statement is used to determine whether a number is used when an error is reported when a character is multiplied by a number!

 
Create Function A (@ STR nvarchar (max) returns intasbegindeclare @ bit; begin try select @ Str * 1; Set @ bit = 1; end trybegin catch set @ bit = 0; end catchreturn @ bit; End
 

After the interview, the more I thought about this method on the road, the more I thought It was inappropriate. There were many problems:
1. The question is a function. Try... Catch cannot be applied in the function;
2. What if it is a hexadecimal, octal, or binary string? Binary or octal respectively composed of 0-1 and 0-7, may be better recognized as a number, but the hexadecimal system will not work, with more A-F these letters, such a get, it is definitely not recognized as a number, but it is indeed a hexadecimal numeric expression!
3. If the function has a return value, an error is returned when writing this statement: "The SELECT statement contained in the function cannot return data to the client ."
4. Print may be used. If print is used, an error is returned: "The use of the 'print' operator with side effects in the function is invalid ."

When I got home with a depressing mood, I tried to write a test on my own because I didn't find many posts or blogs I wanted!

first, consider how to convert an octal or hexadecimal string to a decimal expression (the binary value is temporarily ignored ), I wrote a conversion function by using several hexadecimal transformations! But there is a problem here: How can I determine whether a string is octal, decimal or hexadecimal, here, in order to better enable the Program to handle the hexadecimal conversion, it is handled according to the default hexadecimal expression! For example, the octal value starts with 0, the hexadecimal value starts with 0x, And the other values are in decimal format. The binary value is not considered for the moment!

/*************************************** * ************************************ According input characters, convert to a decimal format that can be used. The values start with octal: 0 and start with hexadecimal: 0x; **************************************** * ***********************************/create function changenum (@ STR nvarchar (max) -- string) returns nvarchar (max) asbegindeclare @ _ temp nvarchar (max), @ I int; Set @ I = 1; -- remove spaces on both sides of the left and right, and all uppercase; set @ STR = ltrim (rtrim (upper (@ Str); Set @ _ temp = ''; -- sixteen If substring (@ STR,) = '0x 'ininwhile @ I <= Len (@ Str)-2 beginif substring (@ STR, @ I +) not between '0' and '9' and substring (@ STR, @ I + 2, 1) not between 'A' and 'F' ininset @ _ temp = 'non-hexadecimal number'; break; endelsebeginselect @ _ temp = @ _ temp + case when substring (@ STR, @ I + 2, 1) = 'A' then 10 * power (cast (16 as bigint), @ I-1) When substring (@ STR, @ I + 2, 1) = 'B' then 11 * power (cast (16 as bigint), @ I- 1) When substring (@ STR, @ I + 2, 1) = 'C' then 12 * power (cast (16 as bigint), @ I-1) When substring (@ STR, @ I + 2, 1) = 'D' then 13 * power (cast (16 as bigint), @ I-1) When substring (@ STR, @ I + 2, 1) = 'E' then 14 * power (cast (16 as bigint), @ I-1) When substring (@ STR, @ I + 2, 1) = 'F' then 15 * power (cast (16 as bigint), @ I-1) elsesubstring (@ STR, @ I + 2, 1) * power (cast (16 as bigint), @ I-1) endendset @ I = @ I + 1; e Ndend -- if substring (@ STR,) = '0' and substring (@ STR )! = 'X' beginwhile @ I <= Len (@ Str)-1 beginif substring (@ STR, @ I, 1) not between '0' and '7' ininset @ _ temp = 'non-octal number'; break; endelsebeginselect @ _ temp = @ _ temp + substring (@ STR, @ I + 1, 1) * power (cast (8 as bigint), @ I-1) endset @ I = @ I + 1; endendreturn @ _ temp; End

After processing the System Conversion problem, you can handle other situations!
For example, there is a number of 123456, which has multiple expressions, such as 123,456; $123456; 123.456, 34, 56; 123456; $, and so on, so I thought of using isnumeric () to determine whether it is a number, but there seems to be a problem. '\' can be recognized as a number. As for some other special symbols, you have not found them yet. You are welcome to point out symbols like this!

 
Create Function isnum (@ STR nvarchar (max) returns varchar (max) asbegindeclare @ errmsg varchar (max); Set @ STR = ltrim (rtrim (@ Str); -- 1. determines whether the input string contains multiple decimal points. if (select charindex ('. ', left (@ STR, Len (@ Str)-charindex ('. ', reverse (@ Str), 1)> 0 beginset @ errmsg =' the input string contains multiple decimal points '; ENDELSEBEGIN--2. determines whether the first character is a comma (,). At this time, the comma can be used as a money separator, without comparing the positions of the comma, such as 123,456 and 123,4567; If (charindex (',', @ STR, 1) = 1) beginset @ errmsg = 'Incoming The first character of the string is comma '; endelsebegin -- determines whether the string is a number based on the result returned in hexadecimal notation; If DBO. changenum (@ Str) = ''beginif isnumeric (@ Str) = 1 and @ Str! = '\' Ininset @ errmsg = 'the incoming characters are numbers:' + @ STR; endelsebeginset @ errmsg = 'the incoming characters are not numbers;'; endendelsebeginset @ errmsg = DBO. changenum (@ Str); endendendreturn @ errmsg; endgo
 

the above is a test version that determines whether the character is a number. The function is not complete yet. I will continue to improve this function if I have time to complete it, I also hope that you can give me more comments and help me modify this function to strive for perfection!