Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: remove last 6 char from string

    Can anyone show me how to remove the last six characters from a string? I am able to identify te last 6 using this: (RIGHT(string,6))... however now I need to be able to remove those last six characters.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What result do you want, if length of the string was less than 6?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    One solution might involve the LENGTH and SUBSTR functions and an arithmetic operator.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought two possibilities.

    If the length of the string was less than 6,
    Policy a: return zero length string
    Policy b: return the original string.
    Because, RIGHT(string,6) returns a string of length 6 padded by blanks.
    Then, removing RIGHT(string,6) from right of the string would be nothing was removed.

    Some examples of the two possibilities.
    Code:
    STRING          LENGTH      POLICY_A        POLICY_B       
    --------------- ----------- --------------- ---------------
    abcdefghijklmno          15 abcdefghi       abcdefghi      
    abcdefg                   7 a               a              
    abcdef                    6                                
    abcde                     5                 abcde          
    abc                       3                 abc            
    a                         1                 a              
                              0

    cartjohn,

    which do you want?
    or
    do you want other results?
    Last edited by tonkuma; 03-30-13 at 18:48.

  5. #5
    Join Date
    Feb 2004
    Posts
    7

    remove last 6 char from string

    Thanks so much for you reply. The result I am looking for is detailed below:

    Orginal_Field New_Field
    abcd_just_201303 abcd_just_
    WRK_Sample_PRF_05 WRK_Sample_PRF_05
    USA_CHANNEL_VW USA_CHANNEL_VW
    AP_MASTER_201002 AP_MASTER_

    so I only want to remove the last 6 char when they are numeric.

    Thanks in advance

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please clarify your reqirements concretely/exhausitively...
    Your requirements wrote in your first post and in newest post were something inconsisten.

    You wrote in the first post
    >> to remove the last six characters from a string <<

    but, in the newest post, you wrote
    >> to remove the last 6 char when they are numeric<<.

    If your requirements were like in the newest post...
    Code:
     
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data
    ( Orginal_Field , New_Field ) AS (
    VALUES
      ( 'abcd_just_201303'  , 'abcd_just_'        )
    , ( 'WRK_Sample_PRF_05' , 'WRK_Sample_PRF_05' )
    , ( 'USA_CHANNEL_VW   ' , 'USA_CHANNEL_VW'    )
    , ( 'AP_MASTER_201002'  , 'AP_MASTER_'        )
    , ( 'AP_MASTER_20102'   , 'AP_MASTER_20102'   )
    , ( 'A1234567'            , 'A1'              )
    , ( 'A1B34567'            , 'A1B34567'        )
    )
    SELECT Orginal_Field
         , New_Field      AS expected_New_Field
         , CASE 
           WHEN TRANSLATE(RIGHT(Orginal_Field , 6) , '' , '0123456789' , '*')
              = '******'
           THEN LEFT(Orginal_Field , LENGTH(Orginal_Field) - 6)
           ELSE Orginal_Field
           END  AS calculated_result
     FROM  test_data;
    ------------------------------------------------------------------------------
    
    ORGINAL_FIELD     EXPECTED_NEW_FIELD CALCULATED_RESULT
    ----------------- ------------------ -----------------
    abcd_just_201303  abcd_just_         abcd_just_       
    WRK_Sample_PRF_05 WRK_Sample_PRF_05  WRK_Sample_PRF_05
    USA_CHANNEL_VW    USA_CHANNEL_VW     USA_CHANNEL_VW   
    AP_MASTER_201002  AP_MASTER_         AP_MASTER_       
    AP_MASTER_20102   AP_MASTER_20102    AP_MASTER_20102  
    A1234567          A1                 A1               
    A1B34567          A1B34567           A1B34567         
    
      7 record(s) selected.
    Note: I added last three examples(or rows).
    Last edited by tonkuma; 04-01-13 at 16:52.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •