Alpha and Numeric data in same column

Transact-SQL
1

Hello.

I have an interesting issue that I am pretty sure others have run into. I have a column that contains alpha, numeric, and date data. I am trying to identify the different types so I can filter it a bit better and running into a wall of frustration.

This is what I have:

CASE When lp.LoanPropertyValue Between '1990-01-01' AND '3000-01-01' THEN 0
** When lp.LoanPropertyValue LIKE '%[a-z]%' THEN 0**
** Else lp.LoanPropertyValue**
** End,**

Which gives me
Conversion failed when converting the varchar value '05/01/2026' to data type int.

2

If you want a date result, you need to cast the 0 as date:

CASE When lp.LoanPropertyValue Between '1990-01-01' AND '3000-01-01' THEN **CAST(**0 AS date)

3

Thank you sir.

Actually, I want it to return zero if it is an alpha or a date and if not then the data that is actually in that column.

Thanks for your feedback.

4

If you want to have different data types in the same column, then you will have to cast as a sql_variant, or convert them all to character:

CASE When lp.LoanPropertyValue Between '1990-01-01' AND '3000-01-01' THEN CAST(0 AS sql_variant)

5

Try:

SELECT CASE 
      WHEN IsDate(lp.LoanPropertyValue)=1 
      OR  lp.LoanPropertyValue LIKE '%[a-z]%' THEN '0' 
      ELSE lp.LoanPropertyValue 
END
6

Thank you both for responding.

Both provide me the desired result.

However, now my issue is there is also some alpha data in the columns that I also want to reflect as zero.

I am using
CASE WHEN IsDate(lp.LoanPropertyValue)=1 THEN '0'
When lp.LoanPropertyValue LIKE '%[a-z]%' THEN 0
Else lp.LoanPropertyValue
End,

Then I get the message below:
Conversion failed when converting the varchar value '208292.00' to data type int.

7

Thanks Scottpletcher. I used the sql_variant for the alpha data and it worked.

Thanks to both of you.

8

I am so sorry ... have another issue/question.

So I am trying to also replace any value >900 with zero. I have tried both of the following and received the same result.

When lp.LoanPropertyValue>'900' THEN Cast(0 AS sql_variant)

When lp.LoanPropertyValue Between '900' AND '1000000000' Then Cast (0 AS sql_variant)

Both are met with:
Conversion failed when converting the varchar value '208292.00' to data type int.