TSQL – Cast string to integer or return default value

by

Yes :). Try this:

DECLARE @text AS NVARCHAR(10)

SET @text="100"
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns 100

SET @text="XXX"
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns NULL

ISNUMERIC() has a few issues pointed by Fedor Hajdu.

It returns true for strings like $ (is currency), , or . (both are separators), + and -.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. to select all the employees who have joined most recently under king
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. Simple way to transpose columns and rows in SQL?
  6. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  7. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  8. SQL JOIN vs IN performance?
  9. Convert varchar into datetime in SQL Server
  10. TSQL Pivot without aggregate function
  11. How to generate a range of dates in SQL Server
  12. SQL RANK() versus ROW_NUMBER()
  13. Generate SQL Create Scripts for existing tables with Query
  14. How do I delete from multiple tables using INNER JOIN in SQL server
  15. Check if a row exists, otherwise insert
  16. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  17. join comma delimited data column
  18. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  19. Alter user defined type in SQL Server
  20. Store multiple bit values in a single table column
  21. SQL Server equivalent to MySQL enum data type?
  22. How to correctly insert newline in nvarchar [duplicate]
  23. Grant SELECT permission on a view, but not on underlying objects
  24. How do you copy a record in a SQL table but swap out the unique id of the new row?
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. SQL – Call Stored Procedure for each record
  27. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment