Extract data after 2nd last space up to 10 characters

Question

How to extract data after 2nd last space up to 10 characters from a SQL Server column?

Column data is below:

INV PNI S000060803 1
INV PNI  P000035365 132
INV PRVC  S000059468 4
INV PIV P000034785 2
INV PIV  T000034785 27

Desired result is:

[Using T-SQL, return nth delimited element from a string](https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string) — Thom A, Jun 09 '23 at 15:30
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Jun 09 '23 at 16:06

Sergey Olontsev · Accepted Answer · 2023-06-09T16:00:09.423

0

You can use syntax working with all versions of SQL Server like this. It's a little bit messy, but should work on any version starting with 2005 at least. I'm using CTE just for demo to create virtual table.

with tmp as (
    select 'INV PNI S000060803 1' as c
    union all select 'INV PNI  P000035365 132'
    union all select 'INV PRVC  S000059468 4'
    union all select 'INV PIV P000034785 2'
    union all select 'INV PIV  T000034785 27'
)
select SUBSTRING(REVERSE(SUBSTRING(REVERSE(c), 0, CHARINDEX(' ', REVERSE(c), CHARINDEX(' ', REVERSE(c)) + 1))), 0, 11)
from tmp

edited Jun 09 '23 at 16:00

answered Jun 09 '23 at 15:51

Sergey Olontsev

1,084
7
19

An inferior solution that doesn't work on data as follows: `'INV PIV T000034785 27'` . – Yitzhak Khabinsky Jun 09 '23 at 17:43

Yitzhak Khabinsky · Answer 2 · 2023-06-09T16:30:21.940

Please try the following solution based on the PARSENAME() function use.

A minimal reproducible example is not provided.

An assumption is made that the tokens column has just a single space character between words/tokens.

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, tokens VARCHAR(256));
INSERT @tbl (tokens) VALUES
('INV PNI S000060803 1'),
('INV PNI P000035365 132'),
('INV PRVC S000059468 4'),
('INV PIV P000034785 2'),
('INV PIV T000034785 27');
-- DDL and sample data population, end

SELECT t.* 
    , PARSENAME(c, 2) AS result
FROM @tbl AS t
    CROSS APPLY (SELECT REPLACE(tokens, SPACE(1), '.')) AS t1(c);

SQL #2

Implementation when there are multiple spaces between words/tokens.

SELECT t.* 
    , PARSENAME(c, 2) AS result
FROM @tbl AS t
CROSS APPLY  (SELECT TRY_CAST('<r><![CDATA[' + tokens + ']]></r>' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)')) AS t1(x)
CROSS APPLY (SELECT REPLACE(x, SPACE(1), '.')) AS t2(c);

Output

id	tokens	result
1	INV PNI S000060803 1	S000060803
2	INV PNI P000035365 132	P000035365
3	INV PRVC S000059468 4	S000059468
4	INV PIV P000034785 2	P000034785
5	INV PIV T000034785 27	T000034785

score 0 · Answer 3 · answered Jun 09 '23 at 17:43

0

Late answer, but here is another option

Select A.ID
      ,NewVal = reverse(JSON_VALUE(JS,'$[1]'))
 From  YourTable A
Cross Apply (values ('["'+replace(string_escape(reverse( YourColumn ),'json'),' ','","')+'"]') ) B(JS)

answered Jun 09 '23 at 17:43

John Cappelletti

79,615
7
44
66

Please try `'INV PIV T000034785 27'`. – Yitzhak Khabinsky Jun 09 '23 at 17:45
@YitzhakKhabinsky I have to believe OP's data has the structure/pattern as presented – John Cappelletti Jun 09 '23 at 18:01

Extract data after 2nd last space up to 10 characters

3 Answers3