CODEWITHSUNDEEP
sqlsql-server
ZMannion
ZMannion

Reputation: 199

Equivalent function to IFERROR in SQL?

I have column which is a substring of a large amount of text.

I want to pull out the 4 characters that should be the year in YYYY format.

If the text is incorrect it could have a text in it making it a string.

I want to compare the year to the current year and I can't do that as the data types are different.

TRY_PARSE isn't a built in function.

Also tried a few variations of:

SELECT SUBSTRING(Text, 46, 4) AS Year, 
CASE WHEN CAST(SUBSTRING(Text, 46, 4) AS INT) IS NULL THEN 'Error'
WHEN CAST(Year AS INT) IS NULL < YEAR(GETDATE()) THEN 'In the past'
ELSE 'In the future'
END TEXT 
FROM TABLE


Example column:

    Year
    2011
    2018
    2022
    D200

Example output required:

Output

    'In the past'
    'In the past'
    'In the future'
    'Error'

Upvotes: 3

Views: 55436

Answers (2)

Thom A
Thom A

Reputation: 95830

I think you want TRY_CONVERT or TRY_CAST:

SELECT CASE WHEN TRY_CONVERT(int,[Text]) < YEAR(GETDATE()) THEN 'In the Past'
            WHEN TRY_CONVERT(int,[Text]) = YEAR(GETDATE()) THEN 'Current Year'
            WHEN TRY_CONVERT(int,[Text]) > YEAR(GETDATE()) THEN 'In The Future',
            ELSE 'Error'
      END
FROM YourTable;

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

Assuming any year between 0000 and 9999 be acceptable as valid years, you could just use LIKE '[0-9][0-9][0-9][0-9]':

SELECT
    CASE WHEN SUBSTRING(Text, 46, 4) NOT LIKE '[0-9][0-9][0-9][0-9]'
         THEN 'Error'
         WHEN CAST(SUBSTRING(Text, 46, 4) AS INT) < YEAR(GETDATE())
         THEN 'In the past'
         ELSE 'In the future' END AS output
FROM yourTable;

Upvotes: 2

Related Questions