show 2 values from single value

i have column with value ProductId - 102 ,but when i want to show this as ProductId - 1 Dev开发者_高级运维iceId - 02

how i will show it?

how the query will be?

thanks in advance.

These assume device id is the rightmost 2 digits

varchar column. If char, cast to varchar and LTRIM

SELECT
    REVERSE(SUBSTRING(REVERSE(MyCol), 3, 8000) AS ProductID,
    RIGHT(MyCol, 2) AS DeviceID

integer column

SELECT
    MyCol / 100 AS ProductID,  --integer arithmetic
    MyCol % 100 AS DeviceID -- modulo

following should help you

select 'ProductId - 102','ProductId -'+left(right('ProductId - 102',3),1),'DeviceId -'+ right('ProductId - 102',2)

You will find that with SQL there are many ways to achieve the same objective. From a standpoint of better coding you really should have ProductID and DeviceID in 2 columns and then if you ever have to display them as 1 together you can combine them. This way in searches you can quickly find DeviceID 47 without having to parse the whole database out.

Lets assume you have a table called PRODUCTS and ProductID has a value '1' and DeviceID was '02'

All you need to do is

SELECT ProductID + DeviceID as CombinedID

and it will return: ProductID - 102

If you still have a valid business reason to do it the way you want:

Lets assume you have a table called PRODUCTS and ProductID has a value 'ProductID - 102'

If you want the full ProductID use

SELECT ProductID from Products

If you want to seperate out the Product from Device ID

Your query would be SELECT LEFT(ProductID, len(ProductID)-2) + ' Device ID - ' + RIGHT(ProductID,2) from Products

This will give you the ProductID and DeviceID broken out... hope that helps