Get everything from last character until space

Question

Get everything from last character until space

RCRock

Right there with Babe

Points: 718
More actions
July 27, 2021 at 10:21 am

#3914247

Can someone please help me to retrive all value from the string value before space.
The requirement is to get everything from last character of string up to first space
For example:
In a table there is column BANAME and values can be like this
ABC RTRN SERTN AEN SDC 0102394547
ABC Up TYRE BV AEN CGFD 13048020
AEN TYRE Payment 11D
I want to extract value for field BANAME like
0102394547
13048020
11D

Viewing 3 posts - 1 through 2 (of 2 total)

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

Phil Parkin SSC Guru Points: 246938 More actions · Answer 1

You can do this using REVERSE, or a splitter (link). A splitter provides a more flexible and elegant solution, in my opinion:

DROP TABLE IF EXISTS #sample;

CREATE TABLE #sample
(
    BANAME VARCHAR(100)
);

INSERT #sample
(
    BANAME
)
VALUES
('ABC RTRN SERTN AEN SDC 0102394547')
,('ABC Up TYRE BV AEN CGFD 13048020')
,('AEN TYRE Payment 11D');

SELECT s.BANAME
      ,split.Item
FROM #sample s
    CROSS APPLY
(
    SELECT TOP (1)
           ItemNumber
          ,Item
    FROM dbo.DelimitedSplit8K(s.BANAME, ' ')
    ORDER BY ItemNumber DESC
) split;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101073 More actions · Answer 2

I'd use REVERSE because it's considerably less overhead.

SELECT BANAME, RIGHT(BANAME, CHARINDEX(' ', REVERSE(BANAME)) - 1) AS BANAME_VALUE
FROM #sample

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".