CODEWITHSUNDEEP
sqlsubstringvarchar
villiec
villiec

Reputation: 45

Substring in Where clause

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

Upvotes: 4

Views: 36889

Answers (2)

SPFiredrake
SPFiredrake

Reputation: 3892

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.

Upvotes: 3

Hart CO
Hart CO

Reputation: 34784

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.

Upvotes: 1

Related Questions