Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Data type validations Expand / Collapse
Author
Message
Posted Wednesday, November 20, 2013 4:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 1,780, Visits: 5,654
shravan.gavva (11/20/2013)
Hi,

I have two columns in the same table
One is data_value (varchar) and the other is sql_data_type (varchar). Data_value holds info like dates, decimals, etc..
sql_data_type holds info about what type of data should be stored in data_value. How can i write a code so that specific_data_type goes into data_value as mentioned in sql_data_type???


Thank you
gs


As others have said, this is a nasty kludge that needs reworking, but I applaud your desire to at least constrain the values to being valid

This may point you in the right direction...

create table table1
(
data_value varchar (50) ,
sql_data_type varchar (50),
check (
case sql_data_type
when 'DATE' then cast(TRY_PARSE(data_value as date) as sql_variant)
when 'decimal' then cast(try_parse(data_value as decimal) as sql_variant)
else data_value
end is not null
),
check (
TYPE_ID(sql_data_type) is not null
)
)



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1516255
    Posted Wednesday, November 20, 2013 9:47 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 9:57 AM
    Points: 7,080, Visits: 12,571
    TRY_PARSE is the first place my mind went as well. A long-overdue feature of T-SQL. I rolled my own for 2008 using SQLCLR which I use within check constraints to keep the SSIS configurations table tidy. TRY_PARSE simply exposes the static TryParse methods on each of the .NET data type classes.

    I would add one simple refinement. If NULL is unacceptable as a data_value then purge all rows WHERE data_value is NULL and alter the column to be NOT NULL, else, tolerate NULL in the check constraint:

    check ( 
    case sql_data_type
    when 'DATE' then cast(TRY_PARSE(data_value as date) as sql_variant)
    when 'decimal' then cast(try_parse(data_value as decimal) as sql_variant)
    else data_value
    end is not NULL
    OR data_value is null -- << added to MM's check
    )



    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1516310
    Posted Thursday, November 21, 2013 7:28 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, November 21, 2013 9:01 AM
    Points: 8, Visits: 7
    THANKS GUYS ...I GOT IT

    Post #1516435
    Posted Thursday, November 21, 2013 7:30 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, November 21, 2013 9:01 AM
    Points: 8, Visits: 7
    thank you guys...
    i finally did it..

    I used isdate to validate date datatypes
    and isnumeric for others

    i used the same syntax as that of the parse statement.....


    Thank you
    once again

    Post #1516437
    Posted Thursday, November 21, 2013 8:30 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 10:04 AM
    Points: 3,329, Visits: 7,181
    Beware of ISNUMERIC(), you might have some problems if you don't consider all the possibilities.
    http://www.sqlservercentral.com/articles/IsNumeric/71512/
    If you're using SQL Server 2012 or 2014, go for TRY_PARSE,TRY_CONVERT or TRY_CAST.



    Luis C.
    I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

    Forum Etiquette: How to post data/code on a forum to get the best help
    Post #1516455
    Posted Thursday, November 21, 2013 9:08 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, November 21, 2013 9:01 AM
    Points: 8, Visits: 7
    I cannot use parse..

    Using Sql 2008..

    Thanks though it was really great info...
    Post #1516473
    Posted Thursday, November 21, 2013 8:54 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 9:57 AM
    Points: 7,080, Visits: 12,571
    Note that ISDATE() has tons of odd interpretations as well.

    For example, '2013' returns as a valid date:

    SELECT  ISDATE('2013')

    Which makes sense if you consider this returns 2013-01-01:

    SELECT  CAST('2013' AS DATE)

    However, do you think of '2013' by itself as a valid date?

    Meanwhile, TRY_PARSE says '2013' is not a valid date:

    SELECT TRY_PARSE('2013' AS DATE)

    but of course 2013-01-01 is deemed valid:

    SELECT TRY_PARSE('2013-01-01' AS DATE)

    So, choose your validation techniques wisely and do not assume the name of a function matches the behavior that might be implied by your interpretation of that name.


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1516635
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse