Post reply

Query Help

  • November 7, 2012 at 5:43 am

    #393550

    I'm testing on some part of the application .

    My Table structure is like

    create table test (id char (8))

    I want , whenever user insert value ,lets say 1

    it should be stored at the back end as 00000001 and not 1 .

    Is there any built in data type available for this ?

    or any logic?

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • November 7, 2012 at 6:08 am

    #1556739

    there is no datatype

    you need to create a function

  • November 7, 2012 at 6:18 am

    #1556743

    Would have to do something like the following due to the way that CHAR stuffs the white space with a space

    declare @i char(8) = '1'

    select right('0000000'+REPLACE(@i,' ',''),8)

  • November 7, 2012 at 6:28 am

    #1556747

    you can use a standard integer identity, and then simply format the number with preceeding zeros whenever you are displaying it. there's no need to store preceeding zeros...that's just a nice feeling formatting that can be created on the fly.

    alternatively, you can add a calculated column, that uses the same identity column i mentioned to build the char string.

    CREATE TABLE [dbo].[TEST] (

    [IID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [ID] AS right('00000000' + CONVERT(VARCHAR,[iid]),8),

    [OTHERCOLUMNS] VARCHAR(30) NULL)

    insert into test(OtherColumns)

    SELECT 'One Fish' UNION ALL

    SELECT 'Two Fish' UNION ALL

    SELECT 'Red Fish' UNION ALL

    SELECT 'Blue Fish'

    select * from test

    /*

    iid id OtherColumns

    ----------- -------- ------------------------------

    1 00000001 One Fish

    2 00000002 Two Fish

    3 00000003 Red Fish

    4 00000004 Blue Fish

    */

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 7, 2012 at 10:09 am

    #1556855

    Thanks a lot Anthony and Lowell 🙂 ... Thats works just fine for me .

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • November 7, 2012 at 11:49 am

    #1556888

    Hi,

    Value = '12 'or '123', exist?

    DECLARE @ID CHAR(8)

    SET @ID='12'

    SELECT ID= REPLICATE('0',8-LEN(@ID))+@ID

    SET @ID='123'

    SELECT ID= REPLICATE('0',8-LEN(@ID))+@ID

  • November 8, 2012 at 3:11 am

    #1557071

    sanket kokane (11/7/2012)

    I'm testing on some part of the application .

    My Table structure is like

    create table test (id char (8))

    I want , whenever user insert value ,lets say 1

    it should be stored at the back end as 00000001 and not 1 .

    Is there any built in data type available for this ?

    or any logic?

    Why "it should be stored at the back end as 0000001 and not 1"?

    Does your business dictate you the technical implementations?

    It is quite stupid to store numeric in CHAR, if you want numbers from 1 to 99999999, just use DECIMAL(8), or even better just INT. Whenever (if ever) id needs to be reported back to user, format it! INT id will take less space and will perform better in joins.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • November 8, 2012 at 4:20 am

    #1557103

    Eugene Elutin (11/8/2012)

    sanket kokane (11/7/2012)

    I'm testing on some part of the application .

    My Table structure is like

    create table test (id char (8))

    I want , whenever user insert value ,lets say 1

    it should be stored at the back end as 00000001 and not 1 .

    Is there any built in data type available for this ?

    or any logic?

    Why "it should be stored at the back end as 0000001 and not 1"?

    Does your business dictate you the technical implementations?

    It is quite stupid to store numeric in CHAR, if you want numbers from 1 to 99999999, just use DECIMAL(8), or even better just INT. Whenever (if ever) id needs to be reported back to user, format it! INT id will take less space and will perform better in joins.

    Yes there is requirement like this only .

    actually other columns in tables will be depend upon that.

    I'm aware about the effect of fixed length datatypes.

    -----------------------------------------------------------------------------
    संकेत कोकणे

  • November 8, 2012 at 6:15 am

    #1557142

    I'll agree with you!

  • November 8, 2012 at 6:36 am

    #1557150

    ..

    actually other columns in tables will be depend upon that.

    ...

    What does this mean?

    Is your database designed specifically for reporting, so you need to store formatted numeric Id's?

    How business can specify the technical implementation without way to validate and check it?

    Why would actually business case about it?

    Does business provide you with DDL for creating all database objects as part of their requirements?

    Looks very strange to me.

    There is a gap here somewhere in translation of real business requirements to technical ones...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

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