Extract string between delimiters
-
HI
I want to extract string between the delimters where data is as follows:
abc_def_cet_qwe output required in only def , how to extract this substring from the main string.
-
If it is always the second character string that you want to extract do it like this:
DECLARE @text VARCHAR(100) = 'abc_def_cet_qwe 'DECLARE @start_index INT
DECLARE @end_index INT
SELECT @start_index = CHARINDEX('_', @text, 1)
SELECT @end_index = CHARINDEX('_', @text, CHARINDEX('_', @text, 1) + 1)
SELECT SUBSTRING(@text, @start_index + 1, @end_index - @start_index - 1)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
pls try below code....
DECLARE @t table(id int,name varchar(50))
insert into @t(id,name)values(1,'abc_def_cet_qwe'),(2,'abc_dek'),(3,'def_rag'),(4,'pes_rfg_def')
DECLARE @Del1 varchar(50)='def'
select id,case when PATINDEX('%'+@Del1+'%',name)>0 then SUBSTRING(name,PATINDEX('%'+@Del1+'%',name),len(@Del1)) end as Sup
from @t
-
Try this:
Select SubString('abc_def_cet_qwe', (CHARINDEX('_', 'abc_def_cet_qwe', 0) + 1), 3) As NewString
If the length is not fixed then you can use this approach:
Select SubString('abc_def_cet_qwe', (CHARINDEX('_', 'abc_def_cet_qwe', 0) + 1),
(CharIndex('_', RIGHT('abc_def_cet_qwe', (LEN('abc_def_cet_qwe') - (CharIndex('_', 'abc_def_cet_qwe', 0)))), 0) - 1)) As NewString
-
subbareddy542 (8/21/2012)
pls try below code....DECLARE @t table(id int,name varchar(50))
insert into @t(id,name)values(1,'abc_def_cet_qwe'),(2,'abc_dek'),(3,'def_rag'),(4,'pes_rfg_def')
DECLARE @Del1 varchar(50)='def'
select id,case when PATINDEX('%'+@Del1+'%',name)>0 then SUBSTRING(name,PATINDEX('%'+@Del1+'%',name),len(@Del1)) end as Sup
from @t
;-)Or you can do this:
SELECT id, Sup = 'def'
FROM @t
WHERE name LIKE '%def%'
--Vadim R.
-
sanjay831 (8/21/2012)
HII want to extract string between the delimters where data is as follows:
abc_def_cet_qwe output required in only def , how to extract this substring from the main string.
Hi Sanjay,
I have few queries here like
1.Is this is the only data or is it just an example of data ?
2.Is the pattern of data always constant ?
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂 -
Use Jeff Moden's DelimitedSplit8K FUNCTION WHERE item = 2.
You can find that by searching on the FUNCTION's name.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
No The format is not constant, it can be qwerfgt_ser_sdft , ertgfh_fert_dfgt_ghty. I have to extract the string between first 2 underscores onli i.e. "ser" for the first , "fert" for second
-
sanjay831 (8/22/2012)
No The format is not constant, it can be qwerfgt_ser_sdft , ertgfh_fert_dfgt_ghty. I have to extract the string between first 2 underscores onli i.e. "ser" for the first , "fert" for secondUsing this: -
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DelimitedSplit8K]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))BEGIN
DROP FUNCTION [dbo].[DelimitedSplit8K];
END
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
/**********************************************************************************************************************
Purpose:
Split a given string at a given delimiter and return a list of the split elements (items).
Notes:
1. Leading a trailing delimiters are treated as if an empty string element were present.
2. Consecutive delimiters are treated as if an empty string element were present between them.
3. Except when spaces are used as a delimiter, all spaces present in each element are preserved.
Returns:
iTVF containing the following:
ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
Item = Element value as a VARCHAR(8000)
Statistics on this function may be found at the following URL:
http://www.sqlservercentral.com/Forums/Topic1101315-203-4.aspx
**********************************************************************************************************************
Revision History:
Rev 00 - 20 Jan 2010 - Concept for inline cteTally: Lynn Pettis and others.
Redaction/Implementation: Jeff Moden
- Base 10 redaction and reduction for CTE. (Total rewrite)
Rev 01 - 13 Mar 2010 - Jeff Moden
- Removed one additional concatenation and one subtraction from the SUBSTRING in the SELECT List for that tiny
bit of extra speed.
Rev 02 - 14 Apr 2010 - Jeff Moden
- No code changes. Added CROSS APPLY usage example to the header, some additional credits, and extra
documentation.
Rev 03 - 18 Apr 2010 - Jeff Moden
- No code changes. Added notes 7, 8, and 9 about certain "optimizations" that don't actually work for this
type of function.
Rev 04 - 29 Jun 2010 - Jeff Moden
- Added WITH SCHEMABINDING thanks to a note by Paul White. This prevents an unnecessary "Table Spool" when the
function is used in an UPDATE statement even though the function makes no external references.
Rev 05 - 02 Apr 2011 - Jeff Moden
- Rewritten for extreme performance improvement especially for larger strings approaching the 8K boundary and
for strings that have wider elements. The redaction of this code involved removing ALL concatenation of
delimiters, optimization of the maximum "N" value by using TOP instead of including it in the WHERE clause,
and the reduction of all previous calculations (thanks to the switch to a "zero based" cteTally) to just one
instance of one add and one instance of a subtract. The length calculation for the final element (not
followed by a delimiter) in the string to be split has been greatly simplified by using the ISNULL/NULLIF
combination to determine when the CHARINDEX returned a 0 which indicates there are no more delimiters to be
had or to start with. Depending on the width of the elements, this code is between 4 and 8 times faster on a
single CPU box than the original code especially near the 8K boundary.
- Modified comments to include more sanity checks on the usage example, etc.
- Removed "other" notes 8 and 9 as they were no longer applicable.
Rev 06 - 12 Apr 2011 - Jeff Moden
- Based on a suggestion by Ron "Bitbucket" McCullough, additional test rows were added to the sample code and
the code was changed to encapsulate the output in pipes so that spaces and empty strings could be perceived
in the output. The first "Notes" section was added. Finally, an extra test was added to the comments above.
Rev 07 - 06 May 2011 - Peter de Heer, a further 15-20% performance enhancement has been discovered and incorporated
into this code which also eliminated the need for a "zero" position in the cteTally table.
**********************************************************************************************************************/
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover NVARCHAR(4000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
Sample data: -
SELECT stringINTO #yourTable
FROM (VALUES('qwerfgt_ser_sdft'),('ertgfh_fert_dfgt_ghty'))a(string);
Solution: -
SELECT *FROM #yourTable a
CROSS APPLY dbo.DelimitedSplit8K(a.string,'_') split
WHERE split.ItemNumber = 2;
Result: -
string ItemNumber Item--------------------- -------------------- -------
qwerfgt_ser_sdft 2 ser
ertgfh_fert_dfgt_ghty 2 fert
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
This would work in all cases I suppose:
Declare @string Varchar(max) = 'ertgfh_fert_dfgt_ghty'
Select SubString(@string, (CHARINDEX('_', @string, 0) + 1),
(CharIndex('_', RIGHT(@string, (LEN(@string) - (CharIndex('_', @string, 0)))), 0) - 1)) As NewString
-
I know this is an old post but I keep running into this or similar situations. This doesn't always work but when you can this is by far the easiest way of doing this.
If you can guarantee there are always the same number of delimiters, and there are no decimals in the string, and there are 4 or fewer pieces, then the Parsename function works perfectly. It doesn't have to be a SQL Name identifier, you can convert any string's delimiters to decimals and parse out the value that you need. i.e.
select Parsename(replace('a|b|c|d','|','.'),2) as value
value
c🙂
-
Correct. It doesn't always work. If you have a trailing empty string (which is fairly common), for example, it pretty much renders it useless.
SELECT
PARSENAME(REPLACE('a|b|c|','|','.'),1) AS VALUE;
SELECT PARSENAME(REPLACE('a|b|c|','|','.'),2) AS VALUE;
SELECT PARSENAME(REPLACE('a|b|c|','|','.'),3) AS VALUE;
SELECT PARSENAME(REPLACE('a|b|c|','|','.'),4) AS VALUE;--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)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply