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:
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
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) A socialist is someone who will give you the shirt off *someone else's* back.
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
Change is inevitable... Change for the better is not.
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
Change is inevitable... Change for the better is not.
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