none
white space is occured while concat two column RRS feed

  • Question

  • table: samp

    id  price  pricetype

    1   2        1

    2   5        2

     

    function for replacing the price type value

    Create FUNCTION [dbo].[fnPriceType]
    (@pricetype varchar(30))
    RETURNS varchar(30)
    AS
    BEGIN
    declare @Return varchar(30)
    select @return = case @pricetype
    when '1' then '$'
    when '2' then '$ XX OFF'
    when '3' then 'SAVE XX %'
    when '4' then 'FREE'
    when '5' then 'No Charge'
    when '11' then 'No Price'
    else 'N/A'
    end

     

    select Offer=price=price+dbo.fnPriceType(pricetype) from samp

     

    but the output of the result table shows below

          offer

    0         No Price
    5         $ XX OFF
    10        $
    0         No Price
    5         $ XX OFF

     

    i don't know y the white space is occure while concate the price and price type col value .please tell the solution for this and what i'm doing wrong

     

     


    Sudhesh. G
    http://gurucoders.blogspot.com
    Tuesday, February 22, 2011 3:17 PM

Answers

  • hi,

    your sample data and your desired out put doesn't match. When I ignore the issues in your sample code, then I get this test batch:

    USE tempdb ;
    GO
    
    CREATE FUNCTION fnPriceType ( @pricetype VARCHAR(30) )
    RETURNS VARCHAR(30)
    AS
        BEGIN
            DECLARE @Result VARCHAR(30) ;
            SELECT  @Result = CASE @pricetype
                                WHEN '1' THEN '$'
                                WHEN '2' THEN '$ XX OFF'
                                WHEN '3' THEN 'SAVE XX %'
                                WHEN '4' THEN 'FREE'
                                WHEN '5' THEN 'No Charge'
                                WHEN '11' THEN 'No Price'
                                ELSE 'N/A'
                              END ;
            RETURN @Result ;
        END ;
    GO
    
    DECLARE @Sample TABLE
        (
          id INT ,
          price MONEY ,
          pricetype INT
        ) ;
    
    INSERT  INTO @Sample
    VALUES  ( 1, 2, 1 ),
            ( 2, 5, 2 ) ;
    
    SELECT  * ,
            dbo.fnPriceType(pricetype)
    FROM    @Sample ;
    GO
    
    DROP FUNCTION fnPriceType ;
    GO

    Which works fine. btw, why do you use a function instead of a lookup table?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, February 22, 2011 3:29 PM

All replies

  • hi,

    your sample data and your desired out put doesn't match. When I ignore the issues in your sample code, then I get this test batch:

    USE tempdb ;
    GO
    
    CREATE FUNCTION fnPriceType ( @pricetype VARCHAR(30) )
    RETURNS VARCHAR(30)
    AS
        BEGIN
            DECLARE @Result VARCHAR(30) ;
            SELECT  @Result = CASE @pricetype
                                WHEN '1' THEN '$'
                                WHEN '2' THEN '$ XX OFF'
                                WHEN '3' THEN 'SAVE XX %'
                                WHEN '4' THEN 'FREE'
                                WHEN '5' THEN 'No Charge'
                                WHEN '11' THEN 'No Price'
                                ELSE 'N/A'
                              END ;
            RETURN @Result ;
        END ;
    GO
    
    DECLARE @Sample TABLE
        (
          id INT ,
          price MONEY ,
          pricetype INT
        ) ;
    
    INSERT  INTO @Sample
    VALUES  ( 1, 2, 1 ),
            ( 2, 5, 2 ) ;
    
    SELECT  * ,
            dbo.fnPriceType(pricetype)
    FROM    @Sample ;
    GO
    
    DROP FUNCTION fnPriceType ;
    GO

    Which works fine. btw, why do you use a function instead of a lookup table?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, February 22, 2011 3:29 PM
  • What is the type of the price field in the table? You can explicitly cast it to varchar(30) also, e.g.

    Offer = cast(Price as Varchar(30)) + dbo.fnPriceType(PriceType)

    BTW, I've never seen the way you did, as 

    Offer = Price = ...

    I'm going to try it right now.

    UPDATE. It gave me an error, so I assume that double = in your query is a typo.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, February 22, 2011 3:31 PM
    Moderator