Substring in Where clause

Question

Below is my query, I am looking to pull records where only the substring value (which will be YYYY) is less than current year - 25 years.... and I should mention that this field is varchar and probably needs to be converted which I haven't been able to do successfully either.

SELECT 
            AccountNumber,
            LoanPrimeLongName,
            convert (varchar,LoanOpenDate,103)LoanOpenDate,
            LoanOriginalBalance,
            LoanBalance,
            LoanInterestRate,
            LoanRemainingTermMonths,
            LoanDelqDays,
            LoanDescription
FROM 
            ARCU.ARCULoanDetailed 
WHERE 
            (((LOANTYPE = '15'          OR
            LOANTYPE = '16'         OR
            LoanType = '17')            AND
            LoanStatus = 'Open')        AND
            ProcessDate = (CONVERT(VARCHAR, GETDATE(), 112)-1)) AND
            (SUBSTRING (loandescription,1,4) not like '%[^0-9]%')

ORDER BY AccountNumber

Select the text, then either press the `{}` button, or press `ctrl+k`. — Brad Christie, Aug 21 '13 at 16:31

score 3 · Accepted Answer · answered Aug 21 '13 at 20:24

Since we don't know which RDBMS you are using, I'm going to go with the simplest answer. This assumes you're using MSSQL.

Use the ISNUMERIC() function to determine if SUBSTRING(loandescription,1,4) is actually a number. If it is, then you can cast/convert it at that point and compare it to the "year" you're interested in. IE:

        ...
        ProcessDate = (CONVERT(VARCHAR, GETDATE(), 112)-1)) AND
        (ISNUMERIC(SUBSTRING(loandescription,1,4)) = 1 AND 
        CAST(SUBSTRING(loandescription,1,4) AS INT) = (YEAR(GETDATE()) - 25))

Due to boolean short circuiting, if the first 4 characters AREN'T numeric, then it won't bother casting to compare. Same rule applies with other RDBMS systems (such as MySQL, PostgreSQL, SQLite, etc), but the methods might be a bit different. In fact, I don't think MySQL or PostgreSQL even have the ISNUMERIC function, meaning you need to find other ways (Regex) to test.

Because of how the SQL Server optimizer works (most SQL optimizers do *not* use short-circuiting, see here: http://stackoverflow.com/a/1445911/109122), this isn't necessarily safe. You need to put the test(`ISNUMERIC`) in a `CASE WHEN` conditional expression and then hide the usage (`CAST`) in its `THEN` clause to be safe. — RBarryYoung, Aug 21 '13 at 20:45

Hart CO · Answer 2 · 2013-08-21T19:59:36.183

1

Assuming your 'YYYY' field is (SUBSTRING (loandescription,1,4) not like '%[^0-9]%'):

AND CASE WHEN SUBSTRING (loandescription,1,4) not like '%[^0-9]%' 
            THEN CAST(SUBSTRING(loandescription,1,4)AS INT 
            ELSE YEAR(GETDATE()) 
     END < YEAR(DATEADD(year,-25,GETDATE()))

You need the CASE statement since the year isn't always populated, and text values won't CAST as INT. Then you use the GETDATE() function in conjunction with DATEADD() to add in your 25 year criteria.

edited Aug 21 '13 at 19:59

answered Aug 21 '13 at 16:35

Hart CO

34,064
6
48
63

I am pulling a substring form the description EXAMPLE '2005 Chevrolet Camaro' I am trying to substring the year, and then pull only values 25 years old. I keep getting the error that "conversion failed when converting the varchar value "HOME" to data type int I apologize for being vague, I am not sure what all information will help, especially without knowing the database, I have some background in SQL but am not a guru in it yet... – villiec Aug 21 '13 at 19:48
Ah, so it sounds like the problem is that your year is not always populated. See updated. – Hart CO Aug 21 '13 at 19:59

Substring in Where clause

2 Answers2