none
Space in Persisted Column in SQL

    Question

  • Hi,

    I am trying to create a persisted column with 2 other columns such as Item_number which value is 715902 and Sku_size which value is 780  XS    .

    Here is my code for computed column,

    [SKU_CD]  AS ((([ITEM_NUMBER]+substring([SKU_SIZE],(1),(3)))+substring([SKU_SIZE],(6),(3)))+substring([SKU_SIZE],(11),(3))) PERSISTED

    But my output column has a space in the middle like  715902      780XS    

    Can someone let me know how to get rid of the space in the middle?

    Thanks

    Tuesday, October 01, 2013 7:18 PM

Answers

  • REPLACE() should be sufficient:

    SKU_CD AS REPLACE(ITEM_NUMBER + SKU_SIZE , ' ', '') PERSISTED

    p.s. I hope that your concrete table definiton uses NOT NULL for ITEM_NUMBER and SKU_SIZE. Otherwise you need

    SKU_CD AS REPLACE(COALESCE(ITEM_NUMBER, '') + COALESCE(SKU_SIZE, '') , ' ', '') PERSISTED


    Tuesday, October 01, 2013 8:32 PM

All replies

  • I'm not sure why you need SUBSTRING(), you can use REPLACE(yourExpression, ' ', '') to remove spaces.
    Tuesday, October 01, 2013 7:33 PM
  • If ITEM_NUMBER is a character column (not a varchar), then I suggest to use RTRIM() function to remove extra spaces.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, October 01, 2013 7:34 PM
    Moderator
  • HI Naomi,

    ITEM_NUMBER and SKU_SIZE both are varchar

    Tuesday, October 01, 2013 7:35 PM
  • HI Stefan,

    I am not sure about the REPLACE FUNCTION, can you give me an example?

    Thanks

    Tuesday, October 01, 2013 7:36 PM
  • There is also a possibility they were saved with extra spaces. RTRIM will not hurt to make sure there are no extra spaces.

    Also, you're using 3 characters in substring but looks like the column has only two characters? You may want to add RTRIM to all expressions then to make sure to not add extra unwanted spaces.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, October 01, 2013 7:39 PM
    Moderator
  • See REPLACE() for some examples.

    Tuesday, October 01, 2013 7:50 PM
  • Hi Naomi,

    I did something like this, but its not working?

    [SKU_CD]  AS ((([ITEM_NUMBER]+LTRIM(substring([SKU_SIZE],(1),(3))))+LTRIM(substring([SKU_SIZE],(6),(3))))+LTRIM(substring([SKU_SIZE],(11),(3)))) PERSISTED

    I even tried using RTRIM no luck?

    Tuesday, October 01, 2013 7:51 PM
  • It looks like REPLACE is used to change the values in the column, but here i am trying to concatenate 2 columns into one.I am not sure if it works here?

    Thanks

    Tuesday, October 01, 2013 7:53 PM
  • I was suggesting RTRIM, but if you always want to remove spaces, just use Stefan's suggestion.

    [SKU_CD] AS replace(Item_Number + LEFT(SKU_SIZE,3) + ..., ' ', '')


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Tuesday, October 01, 2013 7:59 PM
    Moderator
  • Hi Naomi,

    I did something like this

    [SKU_CD] AS replace([ITEM_NUMBER] + LEFT([SKU_SIZE],3) + RIGHT([SKU_SIZE],3) , ' ', '') PERSISTED

    But i only get 781 from Sku size i dont get the values after space like AFZ

    .( the whole value of sku size is 781 AFZ)

    Any Suggestions?

    Thanks

    Tuesday, October 01, 2013 8:15 PM
  • Can you post your table structure and several samples and desired output? Better if you do this as a script I will be able to run (e.g. create table and insert statements). This way I will be able to make a suggestion.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, October 01, 2013 8:25 PM
    Moderator
  • Hi Naomi,

    Here is a sample data

    Create Table test

    (

    [ITEM_NUMBER] [varchar] (20) NULL,
    [SKU_SIZE] [varchar](20) NULL,
    [SKU_CD] AS replace([ITEM_NUMBER] + LEFT([SKU_SIZE],3) + RIGHT([SKU_SIZE],3) , ' ', '') PERSISTED

    )

    The sample values of Item_Number are 

    715902      
    714913      
    718020      

    The sample values of Sku_Size are 

    780  XS       
    801  12P      
    780  XSP      

    The desired output for SKU_CD is 

    715902780XS        
    71491380112P        
    718020780XSP   

    If you have any questions please let me know .

    Thanks

    Tuesday, October 01, 2013 8:28 PM
  • REPLACE() should be sufficient:

    SKU_CD AS REPLACE(ITEM_NUMBER + SKU_SIZE , ' ', '') PERSISTED

    p.s. I hope that your concrete table definiton uses NOT NULL for ITEM_NUMBER and SKU_SIZE. Otherwise you need

    SKU_CD AS REPLACE(COALESCE(ITEM_NUMBER, '') + COALESCE(SKU_SIZE, '') , ' ', '') PERSISTED


    Tuesday, October 01, 2013 8:32 PM
  • Hi Stefan

    It worked.

    Thanks

    Tuesday, October 01, 2013 8:34 PM