March 18, 2009 at 4:04 pm
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
March 18, 2009 at 5:53 pm
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...
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]
March 18, 2009 at 5:54 pm
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.
March 18, 2009 at 6:07 pm
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