Return string between 2 characters from the end of the string

  • I have data in a field that looks like this: 'INV CRT IS15000467 1', 'INV CSH 144934 1', 'INV CSH IS15000134 1', 'INV CSH IS15000442 10'. I am trying to figure out how to return the string between the last space and the second to last space in the string: 'IS15000467', '144934', 'IS15000134', 'IS15000442'. I can find plenty of examples of how to return the string from the front of the string but not in reverse. I believe I need to do this in reverse because the space between the first part of the string and the middle isn't consistent.

  • If you don't have periods and the lengths of your strings are not over 128 chars per section, this can be an option.

    SELECT PARSENAME(REPLACE('INV CRT IS15000467 1', ' ', '.'), 2)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for trying. This code only returns NULL in my query.

  • Please post DDL and the values that give NULL as a result.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Here is what I tried:

    SELECT [site_ref]

    ,[trans_num]

    ,[acct]

    ,[trans_date]

    ,[dom_amount]

    ,[ref]

    --,SUBSTRING( [ref], charindex(' ',[ref],1) + 2, charindex(' ',[ref],1) - charindex(' ',[ref],1) - 1 ) as 'TEST'

    --,SUBSTRING( '123@yahoo.com', charindex('@','123@yahoo.com',1) + 1, charindex('.','123@yahoo.com',1) - charindex('@','123@yahoo.com',1) - 1 )

    ,PARSENAME(REPLACE([ref], ' ', '.'), 2) 'TEST'

    FROM [ITEC_App].[dbo].[ledger_mst]

    WHERE [acct] = '43000'

    and

    [control_year] = '2016'

    order by ref

    GO

    And here is some sample data:

    ITEC509804430002016-05-19 10:59:15.000-64.63000000INV CRT IR00000005 2NULL

    ITEC370138430002016-02-04 09:41:53.000-3008.02000000INV CRT IS15000467 1NULL

    ITEC377928430002016-02-08 07:01:12.00030717.03000000INV CSH 144934 1NULL

    ITEC356272430002016-01-25 15:25:34.000-29005.39000000INV CSH 144934 1NULL

    ITEC356240430002016-01-25 08:18:04.00030717.03000000INV CSH 144934 1NULL

    ITEC359388430002016-01-27 11:40:10.0009180.81000000INV CSH IS15000134 1NULL

    ITEC359396430002016-01-27 11:46:51.0009180.81000000INV CSH IS15000135 1NULL

  • I'm sorry, but that doesn't help. I need the column definition, apparently you're using a char column, but that's just a guess. Read the articles linked in my signature to know what's needed.

    Here's a longer alternative.

    SELECT LEFT( NewString, CHARINDEX(' ', NewString)), PARSENAME(REPLACE(ref, ' ', '.'), 2) 'TEST'

    --Start of sample data

    FROM (VALUES

    ('INV CRT IR00000005 2'),

    ('INV CRT IS15000467 1'),

    ('INV CSH 144934 1' ),

    ('INV CSH 144934 1' ),

    ('INV CSH 144934 1' ),

    ('INV CSH IS15000134 1'),

    ('INV CSH IS15000135 1'))x(ref)

    --End of sample data

    CROSS APPLY (SELECT STUFF( ref, 1, CHARINDEX(' ', ref, CHARINDEX(' ', ref)+1),''))y(NewString)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • The data type is a nonvarchar. How do I tell it to find the last space in the string?

  • Sorry if this overlaps already-posted code, was busy for a long while before I was able to get back to working on this:

    SELECT string, CASE WHEN next_to_last_space = 0 THEN ''

    ELSE SUBSTRING(string, LEN(string) - next_to_last_space + 2,

    next_to_last_space - last_space) END AS value

    FROM (

    VALUES('INV CRT IS15000467 1'),

    ('INV CSH 144934 1'),

    ('INV CSH IS15000134 1'),

    ('INV CSH IS15000442 10'),

    ('INV CSH IS15000442 xx q rrrrrr e10'),

    ('INV ERR'),

    ('INVE')

    ) AS test_data(string)

    CROSS APPLY (

    SELECT CHARINDEX(' ', REVERSE(string)) AS last_space

    ) AS assign_alias_names1

    CROSS APPLY (

    SELECT CASE WHEN last_space = 0 THEN 0

    ELSE CHARINDEX(' ', REVERSE(string), last_space + 1) END AS next_to_last_space

    ) AS assign_alias_names2

    Edit: Split the "ELSE" code in the main query to separate lines.

    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".

  • Luis Cazares (6/27/2016)


    I'm sorry, but that doesn't help. I need the column definition, apparently you're using a char column, but that's just a guess. Read the articles linked in my signature to know what's needed.

    Here's a longer alternative.

    SELECT LEFT( NewString, CHARINDEX(' ', NewString)), PARSENAME(REPLACE(ref, ' ', '.'), 2) 'TEST'

    --Start of sample data

    FROM (VALUES

    ('INV CRT IR00000005 2'),

    ('INV CRT IS15000467 1'),

    ('INV CSH 144934 1' ),

    ('INV CSH 144934 1' ),

    ('INV CSH 144934 1' ),

    ('INV CSH IS15000134 1'),

    ('INV CSH IS15000135 1'))x(ref)

    --End of sample data

    CROSS APPLY (SELECT STUFF( ref, 1, CHARINDEX(' ', ref, CHARINDEX(' ', ref)+1),''))y(NewString)

    Luis,

    Quote the OPs post where he posted the data and you'll see that the spaces are not where you expect. The sub-fields within the column are all in the same place as in fixed field format. You can successfully grab the info just by using SUBSTRING.

    --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)

  • Here's my approach. Forgive the verbosity, the only way I can think through string manipulation in SQL is to do it in steps.

    -- fictious source data

    ;with src as

    (

    select string = 'INV CRT IS15000467 1' union all

    select 'INV CSH 144934 1' union all

    select 'INV CSH IS15000134 1' union all

    select 'INV CSH IS15000442 10'

    -- Reversal of the string. Doing this because it's easier to find the first of a string than the nth.

    -- Using a CTE (not necessary) because then I can just reference the reversed string as many times as I need

    ), rvrs as

    (

    select rvrsString = reverse(string)

    from src

    -- Cut off the first n characters.

    -- These are all the carachters to the right of the original string.

    -- In the reverse string, they're all the caracters between the start of the string, and the first space.

    ), chomp as

    (

    select chompString = stuff(rvrsString, 1, charindex(' ', rvrsString) + 1, '')

    from rvrs

    )

    -- Now the start of the reverse string is the start of the set of characters we want.

    --Substring that to the next space and that's the string we want

    -- Reverse the whole thing to get the original value

    select reverse(substring(chompString, 1, charindex(' ', chompString) - 1))

    from chomp

    This might not (probably isn't) the most efficient way to do it, but I find it's a good way to break out what operations I want to do.

    Executive Junior Cowboy Developer, Esq.[/url]

  • Thanks, this put me on the right track when you pointed out the string I was looking for showed up in the same location within the string.

  • Jeff Moden (6/27/2016)


    Luis,

    Quote the OPs post where he posted the data and you'll see that the spaces are not where you expect. The sub-fields within the column are all in the same place as in fixed field format. You can successfully grab the info just by using SUBSTRING.

    OMG, that's why we need sample data in a consumable format. I never expected this as it is so obvious.

    We often get into bigger problems by overlooking the obvious solution. 😀

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (6/28/2016)


    Jeff Moden (6/27/2016)


    Luis,

    Quote the OPs post where he posted the data and you'll see that the spaces are not where you expect. The sub-fields within the column are all in the same place as in fixed field format. You can successfully grab the info just by using SUBSTRING.

    OMG, that's why we need sample data in a consumable format. I never expected this as it is so obvious.

    We often get into bigger problems by overlooking the obvious solution. 😀

    Exactly. Readily consumable data and a CREATE TABLE statement answers so many questions. I wish folks would understand that we're not trying to punish them by asking for it.

    --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)

  • Sorry guys my first post on here. I'll try to be better the next time. Thanks for all your help!

  • manderson 20961 (6/28/2016)


    Sorry guys my first post on here. I'll try to be better the next time. Thanks for all your help!

    No problem, as long as you understood the problems of not posting the data correctly. 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 15 (of 18 total)

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