Post reply

Datatypes to accept 00506

  • April 21, 2010 at 12:26 am

    #100245

    Hi experts,

    Is there any datatype that accept 000506 as 000506 and not as 506.

    I know varchar can be used but it accepts catheters also.

    Please help.

    Tanx 😀

  • April 21, 2010 at 1:11 am

    #1155138

    I think char/varchar will suit.

    -- Gianluca Sartori

  • April 21, 2010 at 1:20 am

    #1155140

    constraint that will prevent using any character that is not a digit. Bellow is a demonstration of such a code:

    use tempdb

    go

    --creating the check constraint that limits the values of the column

    create table Demo (vc varchar(6) constraint CK_CheckOnlyDigits check (vc like '[0-9][0-9][0-9][0-9][0-9][0-9]'))

    go

    --inserting leading zeros

    insert into Demo (vc) values ('000560')

    go

    --trying to insert a charactar that is not a digit and failing

    insert into Demo (vc) values ('a')

    go

    select * from Demo

    go

    --cleanup

    drop table Demo

    If the length of the string is the always the same, you can also use one of the numeric data types and then according to it’s length compute how many zeros you should add at the beginning. In any case one might argue that showing the leading zeros is something that the presentation layer should take care about and not necessarily the database.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • April 21, 2010 at 2:14 am

    #1155167

    Thanks a lot Adi,

    it is working fine.

    Do you have any idea how to pass "like wildcard" in DB2.

    --I'm able to create the table.

    create table Demo (vc varchar(6) constraint CK_CheckOnlyDigits check (vc like '[0-9][0-9][0-9][0-9][0-9][0-9]'))

    --but not able to insert following due to check constraint.

    insert into Demo (vc) values ('000560')

    Tanx 😀

  • April 21, 2010 at 2:19 am

    #1155175

    Sorry, I never worked with DB2, so I can't help you with that.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply