CODEWITHSUNDEEP
sqlsql-server
swathi
swathi

Reputation: 417

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.

Upvotes: 0

Views: 297

Answers (2)

trincot
trincot

Reputation: 350280

You could take the rightmost 8 characters, inject two hyphens with stuff(), and check if that is a valid date with isdate(). If so, repeat the same and make the conversion with cast():

select case when len(policy_date) >= 8 
                 and isdate(stuff(stuff(right(policy_date, 8), 7, '-'), 5, '-')) then
            cast(stuff(stuff(right(policy_date, 8), 7, '-'), 5, '-') as datetime)
       end

Upvotes: 0

Joe C
Joe C

Reputation: 3993

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.

Upvotes: 2

Related Questions