June 27, 2016 at 12:14 pm
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.
June 27, 2016 at 12:20 pm
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)
June 27, 2016 at 12:26 pm
Thanks for trying. This code only returns NULL in my query.
June 27, 2016 at 12:52 pm
Please post DDL and the values that give NULL as a result.
June 27, 2016 at 12:59 pm
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:
ITEC 509804 43000 2016-05-19 10:59:15.000 -64.63000000 INV CRT IR00000005 2 NULL
ITEC 370138 43000 2016-02-04 09:41:53.000 -3008.02000000 INV CRT IS15000467 1 NULL
ITEC 377928 43000 2016-02-08 07:01:12.000 30717.03000000 INV CSH 144934 1 NULL
ITEC 356272 43000 2016-01-25 15:25:34.000 -29005.39000000 INV CSH 144934 1 NULL
ITEC 356240 43000 2016-01-25 08:18:04.000 30717.03000000 INV CSH 144934 1 NULL
ITEC 359388 43000 2016-01-27 11:40:10.000 9180.81000000 INV CSH IS15000134 1 NULL
ITEC 359396 43000 2016-01-27 11:46:51.000 9180.81000000 INV CSH IS15000135 1 NULL
June 27, 2016 at 1:11 pm
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)
June 27, 2016 at 1:18 pm
The data type is a nonvarchar. How do I tell it to find the last space in the string?
June 27, 2016 at 1:49 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 27, 2016 at 8:31 pm
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
June 27, 2016 at 8:53 pm
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.
June 28, 2016 at 6:48 am
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.
June 28, 2016 at 7:00 am
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. 😀
June 28, 2016 at 7:31 am
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
June 28, 2016 at 8:57 am
Sorry guys my first post on here. I'll try to be better the next time. Thanks for all your help!
June 28, 2016 at 9:22 am
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. 😉
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply