SQLTeam.com | Weblogs | Forums

Alpha and Numeric data in same column

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.

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)

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.

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)

Try:

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

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.

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

Thanks to both of you.

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.