Table

  • I have this table currently

    Col1 Col2

    400 depA

    401 depB

    402 depC

    403 depD

    404 depE

    500 depF

    501 depG

    502 depH

    503 depI

    504 depJ

    Now my front end application has a drop down list which lists these two columns together . I mean my list would show 400-depA,400-depB etc together.I select one of the dept(400-depA ) in this drop down list.

    Now I need to add 4 before the 400,401,402,403,404 departments , similarly 5 before the 500 depF

    501 , 502 ,503 ,504 departments . SO in the drop down list it would show up as (4400-depA....... 5500-depF....)

    How do I achieve this ? Should I create one more table or is there any other way of achieving this ?

    Thanks

  • Without table structure .. I would have to say this would work for you:

    CREATE TABLE #test (col1 INT)

    INSERT INTO #test VALUES (400)

    INSERT INTO #test VALUES (401)

    INSERT INTO #test VALUES (402)

    INSERT INTO #test VALUES (403)

    INSERT INTO #test VALUES (500)

    INSERT INTO #test VALUES (501)

    INSERT INTO #test VALUES (502)

    INSERT INTO #test VALUES (503)

    SELECT * FROM #test

    UPDATE #test

    SET col1 = CAST((CAST(LEFT(col1,1) AS VARCHAR) + CAST(col1 AS VARCHAR)) AS INT)

    SELECT * FROM #test

    Edit: Fixing spacing issues...

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • I'm assuming Col1 is a character field. If not you'll need to cast/convert it to a char.

    If the value is always the same as the first character, use:

    replicate(left(Col1, 1), 2) + Col1 + '-' + Col2

    If the value can vary, you'd need to use a case statement, eg.

    case

    when left(Col1, 1) = '4' then '4'

    when left(Col1, 1) = '5' then '5'

    ...

    end + Col1 + '-' + Col2

    (NB. untested code, but you should get the idea)

    Of course it would be easier to change the table's data/structure so Col1 included the additional character "natively", but that depends on how else that is used.

  • This is exactly what should be done in UI code.

    What's the point of populating drop-down box with values which you cannot match back to values in table?

    After a user selected one of them - how are you gonna explain database which item is selected?

    _____________
    Code for TallyGenerator

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

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