none
Create a query to remove last two characters of a string RRS feed

  • Question

  • I am writing a Sql query where I need to remove the last two characters of a string. The string length is variable (6 or 7).

    The following query works for the 6 character string

    SELECT SUBSTRING(Col1,-1,LENGTH (Colname) -2) AS COL_1, COL2 AS COL_2, COL3 AS COL_3
    FROM lib.Table
    LEFT JOIN Lib2.Table2 ON lib.Table.Field1=lib2.table2.Field2 AND lib.table.Field3=lib2.Table2.Field3

    WHERE Field1=180 AND Field4='A' AND COL3 <> '' AND Field5 = 'Field_5' AND Col1<999999;
    ORDER BY COL1;                                                                                                                                                                      The following works for the 7 character string

    SELECT SUBSTRING(Col1,0,LENGTH (Colname) -2) AS COL_1, COL2 AS COL_2, COL3 AS COL_3
    FROM lib.Table
    LEFT JOIN Lib2.Table2 ON lib.Table.Field1=lib2.table2.Field2 AND lib.table.Field3=lib2.Table2.Field3

    WHERE Field1=180 AND Field4='A' AND COL3 <> '' AND Field5 = 'Field_5' AND Col1<999999;
    ORDER BY COL1;

    How can I write this so it works for both string length or how do I link these two queries together?

    For the seven character string records, the last three characters are removed, instead of 2 when I use the first query.

    I'm a newbie, I hope this makes sense. Thank you

    Thursday, January 9, 2014 10:44 PM

Answers

All replies