swathi swathi - 4 months ago 10
SQL Question

find the len of the field and update the field if the len is less than 8 in the column

I Have a column policy_date with datatype varchar

Policy_date
-----------
A1999062
20010405


I would like to update the data with
case when isnumeric(policy_date) = 0 then trim the alphabet (1999062) and check if the len is less than 8 then make it as NULL else Policy_date.

NOTE: i have to convert the date field to
date
datatype instead of
varchar
.
My out put should look like:

Policy_Date
-----------
NULL
2001-04-05


Can anyone write the sql case statement .
Thank You,
Swathi.

Answer

Based on your comment you just want to remove the first character position if it is alpha. That eliminates the need for a function / CLR call. If this requirement changes you will need to create a function to strip the chars but the rest of the query will work.

Create Table #Test (PolicyDate Varchar(8000))
Insert #Test Values ('A19990602')
Insert #Test Values ('A1999062')
Insert #Test Values ('20010405')

Select  Convert(Date,
        Case When IsNumeric(PolicyDate) = 0 Then 
             Case When Len(PolicyDate) < 9 Then Null
                Else Right(PolicyDate, Len(PolicyDate) -1) End
            Else PolicyDate
            End) PolicyDate2
    From #Test 

Example function, there are many topics available on which approach is faster.

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

ps. I used IsNumeric() because that is what you said you wanted to implement. However take note that by design certian characters can be included and still have the function return that it is a number. IsNumeric($1,230.54) will return a 1. If that is a concern then use an alternative, for example TRY_PARSE(PolicyDate as int) for sql2012 and above.