Home 2018 November SQL Puzzle: SQL Advance Query – Check a String Is Number or Not

SQL Puzzle: SQL Advance Query – Check a String Is Number or Not

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below sample solutions and for checking the string is a number or not in SQL Server.

Solution 1:

Solution 2:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Server: Script to find the list of TriggersSQL Server: Script to find the Size of Indexes
Comments: 3
  1. Dinesh
    November 6, 2018 at 7:45 am

    If used special character your statement is given wrong result.

    DECLARE @str VARCHAR(50)
    SET @str =’8-‘;
    SELECT CASE WHEN @str NOT LIKE ‘%[^0-9.-]%’
    THEN ‘Yes Numeric’ ELSE ‘Not Numeric’ END IsNumber

    GO
    Declare @Str As VarChar(50)
    Set @Str=’8-‘
    Select (Case When (PatIndex(‘%[^0-9%’,@Str)>0 Or PatIndex(‘%[~`!@#$%^&*()-_=+ a-z A-Z]%’,@Str)>0) Then ‘Not Numeric’ Else ‘Yes Numeric’ End)

    ReplyCancel
  2. Dinesh
    November 6, 2018 at 7:49 am

    if we use the “E” validating is missing.

    DECLARE @str NVARCHAR(100) = ‘888E’
    IF(ISNUMERIC(@str) = 1)
    BEGIN
    PRINT ‘Yes Numeric’
    END
    ELSE
    BEGIN
    PRINT ‘Not Numeric’
    END

    GO

    DECLARE @str NVARCHAR(100) = ’88E’
    IF((IsNumeric(@Str)=1 And PatIndex(‘%[a-z A-Z]%’,@Str)<=0))
    BEGIN
    PRINT 'Yes Numeric'
    END
    ELSE
    BEGIN
    PRINT 'Not Numeric'
    END

    ReplyCancel
  3. Nikhil Bhosale
    August 14, 2019 at 9:20 am

    Declare @str nvarchar(100) = ‘8424ij9694’;

    if(Isnumeric(@str) = 1)
    begin
    Select ‘Given string is Numeric !’ AS Result
    end
    else
    begin
    Select ‘Given string is not a Numeric !’ AS Result
    end

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

November 5, 2018 3 Comments SQL Puzzle, , , , , , , , , , ,