How to do this?

I need to combine two fields in a single column in a query like Stock Number
and Product in a way it will look like this:
Stock NumberA - ProductA
Stock NumberB - ProductA
Stock NumberC - ProductA
...and so on
What I want to achieve here is the position of the "-" seperate the Stock
Number and the Product. I want to algin the "-" in the same position.
However the Stock Number is varied in length and I am not sure how to do
this.
ThanksAssuming that both StockNumber and ProductName use character datatypes:
SELECT LEFT(StockNumber + REPLICATE(' ', 20), 20) + '-' + ProductName
FROM YourTable
Apply appropriate cast/convert if they are not...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eXfHLks7FHA.3416@.TK2MSFTNGP15.phx.gbl...
>I need to combine two fields in a single column in a query like Stock
>Number and Product in a way it will look like this:
> Stock NumberA - ProductA
> Stock NumberB - ProductA
> Stock NumberC - ProductA
> ...and so on
> What I want to achieve here is the position of the "-" seperate the Stock
> Number and the Product. I want to algin the "-" in the same position.
> However the Stock Number is varied in length and I am not sure how to do
> this.
> Thanks
>|||Thank you for your reply.
However the width of a character is different like "i" and "w", simply by
adding " " to it can not make the "-" appears at the same location
horizontally.
Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uo7fbos7FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Assuming that both StockNumber and ProductName use character datatypes:
>
> SELECT LEFT(StockNumber + REPLICATE(' ', 20), 20) + '-' + ProductName
> FROM YourTable
>
> Apply appropriate cast/convert if they are not...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Paul" <paul_mak@.hotmail.com> wrote in message
> news:eXfHLks7FHA.3416@.TK2MSFTNGP15.phx.gbl...
>|||That's a font problem. Use a fixed-width font if you need that.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Paul" <paul_mak@.hotmail.com> wrote in message
news:u5$dPdu7FHA.2092@.TK2MSFTNGP12.phx.gbl...
> Thank you for your reply.
> However the width of a character is different like "i" and "w", simply by
> adding " " to it can not make the "-" appears at the same location
> horizontally.
> Thanks.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uo7fbos7FHA.1184@.TK2MSFTNGP12.phx.gbl...
>|||Paul (paul_mak@.hotmail.com) writes:
> Thank you for your reply.
> However the width of a character is different like "i" and "w", simply by
> adding " " to it can not make the "-" appears at the same location
> horizontally.
If you need to cater for proportional fonts, then you have entirely
grown out of SQL Server. Then you need to find client-side solution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.
This is an insane design flaw that will make all your queries, data
audits, etc. ten times harder than they should be . Improve your data
quality by killing the person that did this. Then switch to UPC or EAN
or another fixed length industry code.|||>> However the width of a character is different like "i" and "w", simply by
adding " " to it can not make the "-" appears at the same location horizont
ally. <<
Paul, you are an idiot. Step away from the database. Fonts are
display and not data!! This is soooooooooooo fundamental that I cannot
believe you posted this. I have to use this in a book!