querry for displaying numbers and text seperate from table field using sql server

developerit

hi iam using sql server 2000 in my table orders the fields are companyid int voucherdate smalldate description nvarchar(50) the problem is when i say select * from orders ; it is displaying orders like this 1 1/21/2010 1234orderscomleted 2 2/2/2010 67012requireurgent but i want to display in this format the number with paranthesis and the text 1 1/21/2010 (1234)orderscomleted 2 2/2/2010 (67012)requireurgent can you give example which helps me

Lost User

You can do that in programming language like c# or vb.net but I think it is not possible using SQL.

Jinal Desai

_Damian S_

By the look of your table design, the numbers and text are simply joined in the field "description". This means it will display exactly as the user has input it!! The easiest way would be for your users to input the data in the format you are after, or change your table design so that the number is stored in its own field, then you could simply select the number and append it into some parentheses! eg: select companyid, voucherdate, '(' + ordernumber + ')' as FormattedNumber, description from orders

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

Md Marufuzzaman

If the data are showing; comes from single entry then i would like to suggest you to re-design you database table.

Thanks Md. Marufuzzaman

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

David Skelly

It's not the best database design in the world, so I agree with the others, your first approach should be to change the table design if you can. However, sometimes you don't have that option and the database design is out of your hands, so this link may help: http://www.kf7.co.uk/sql-server-function-get-numeric.aspx[^] You should be able to start from that and get something that works for you. It's not going to be fast, though.