Post reply

Trim string based on characters

  • March 21, 2016 at 3:30 am

    #317090

    Hi,

    I have strings like

    WebApp - 10148

    Smart - App - 1458

    Expected Result

    WebApp

    Smart - App

    I want to trim the characters from - to numbers from right.

    I have tried the below query and the result is this

    WebApp

    Smart

    select LEFT(app+' - ', CHARINDEX(' - ',app+' - ')-1) from repository

    Can anyone assist me sort this?

  • March 21, 2016 at 3:34 am

    #1866394

    Use REVERSE, LEFT and LEN to find the position of the final hyphen, then use LEFT to return all the characters up to that point.

    John

  • March 21, 2016 at 4:06 am

    #1866398

    Can you provide an example, since I am new to sql.

  • March 21, 2016 at 4:06 am

    #1866399

    Can you provide an example? Since I am new to sql.

  • March 21, 2016 at 4:17 am

    #1866400

    Then this is a good learning opportunity for you. You've already shown you can use LEFT and CHARINDEX. Read about LEN and REVERSE and what they do, and see if you can build an expression that will return what you're looking for. Post back if you run into any difficulty and we'll try to help.

    John

  • March 21, 2016 at 5:51 am

    #1866412

    got it!

    reverse(right(reverse(app), len(app) - charindex('-',reverse(app),1)))

  • March 21, 2016 at 10:33 am

    #1866541

    vigneshlagoons 51204 (3/21/2016)

    got it!

    reverse(right(reverse(app), len(app) - charindex('-',reverse(app),1)))

    That uses 3 reverses and that's expensive. I'll try to get back to this tonight.

    Personally, I wouldn't use any reverses for this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 21, 2016 at 3:36 pm

    #1866632

    select LEFT(@var, len(@var)- CHARINDEX('-',REVERSE(@var), 0))

  • March 21, 2016 at 5:42 pm

    #1866642

    -- Sample values in a table

    DECLARE @repository TABLE(someid int, string varchar(100));

    INSERT @repository VALUES (1,'WebApp - 10148'),(2,'Smart - App - 1458'),(3,'No Number - here');

    -- Solution if there's always a number

    SELECT String, NewString = SUBSTRING(string, 1, PATINDEX('% - [0-9]%', string)-1)

    FROM @repository

    WHERE someid <> 3;

    -- Solution for when there isn't always a number

    SELECT

    String,

    NewString = ISNULL(SUBSTRING(string,1,NULLIF(PATINDEX('% - [0-9]%',string),0)-1),string)

    FROM @repository;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply