LEFT and RIGHT of Delimiter

Question

LEFT and RIGHT of Delimiter

SQL_Enthusiast

Hall of Fame

Points: 3805
More actions
May 7, 2013 at 3:48 pm

#273707

I have a value that is '0111~Group Name' in a column called GROUP_NUM in a table called TEMP_TABLE
I need to know how to select everything LEFT of the ~ and then RIGHT of the ~, but not show the TILDE. SELECT LEFT VALUE, RIGHT VALUE FROM TEMP_TABLE...
I tried..., but it failed. Not enough arguements. Thoughts, suggestions, references?
SELECT LTRIM(RTRIM(SUBSTRING([tt].[GROUP_NUM], CHARINDEX('~')))) FROM [dbo].[TEMP_TABLE] AS tt

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

SQL_Enthusiast (5/7/2013)
I have a value that is '0111~Group Name' in a column called GROUP_NUM in a table called TEMP_TABLE
I need to know how to select everything LEFT of the ~ and then RIGHT of the ~, but not show the TILDE. SELECT LEFT VALUE, RIGHT VALUE FROM TEMP_TABLE...
I tried..., but it failed. Not enough arguements. Thoughts, suggestions, references?
SELECT LTRIM(RTRIM(SUBSTRING([tt].[GROUP_NUM], CHARINDEX('~')))) FROM [dbo].[TEMP_TABLE] AS tt

You really need to read the Manual. 🙂

Which is BOL (Books On Line), which is "Help" (F1).

SUBSTRING takes 3 arguements - String, Position From, Position To.

In your case - SUBSTRING([tt].[GROUP_NUM], 1, CHARINDEX('~'))

For the RIGHT part use REVERSE function:

Reverse the sourse string ([tt].[GROUP_NUM]), do the SUBSTRING thing and then reverse the output.

_____________
Code for TallyGenerator

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 2

I did read BOL, along with 50 other articles that really didn't help. So I figured it out on my own. I'm open to suggestions on how to make it better, but for now... it works so I'm smiling 😀

SELECT

[tt].[GROUP_NUM]

, CASE WHEN [tt].[GROUP_NUM] LIKE '%~%'

THEN SUBSTRING([tt].[GROUP_NUM] , 1 ,

CHARINDEX('~' , [tt].[GROUP_NUM]) - 1)

END AS 'LEFT'

, CASE WHEN [tt].[GROUP_NUM] LIKE '%~%'

THEN (SELECT RIGHT([tt].[GROUP_NUM], CHARINDEX('~', REVERSE([tt].[GROUP_NUM])) -1))

END AS 'RIGHT'

FROM

[dbo].[TEMP_TABLE] AS tt

Sergiy SSC Guru Points: 110208 More actions · Answer 3

SQL_Enthusiast (5/7/2013)
I did read BOL, along with 50 other articles that really didn't help. So I figured it out on my own. I'm open to suggestions on how to make it better, but for now... it works so I'm smiling 😀
SELECT
[tt].[GROUP_NUM]
, CASE WHEN [tt].[GROUP_NUM] LIKE '%~%'
THEN SUBSTRING([tt].[GROUP_NUM] , 1 ,
CHARINDEX('~' , [tt].[GROUP_NUM]) - 1)
END AS 'LEFT'
, CASE WHEN [tt].[GROUP_NUM] LIKE '%~%'
THEN (SELECT RIGHT([tt].[GROUP_NUM], CHARINDEX('~', REVERSE([tt].[GROUP_NUM])) -1))
END AS 'RIGHT'
FROM
[dbo].[TEMP_TABLE] AS tt

I would make it differently, but not sure I could make it much better. 🙂

Only note - your CASE statements are missing ELSE parts.

If there is no ~ in the string both LEFT and RIGHT parts will be NULL - is it as intended?

_____________
Code for TallyGenerator

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 4

Yes, no tilde = NULL. That was intended... Thank you

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 5

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[DString] NVARCHAR(255) NULL,

PRIMARY KEY (ID))

INSERT INTO #TempTable

SELECT '0111~Apple' UNION ALL

SELECT '0222~Banana' UNION ALL

SELECT '0333~Carrot' UNION ALL

SELECT '0444~Danish' UNION ALL

SELECT '0555~Eclair'

SELECT

ID

,DString

,dsk1.Item AS Code

,dsk2.Item AS Item

FROM

#TempTable AS tt

CROSS APPLY

dbo.DelimitedSplit8K(tt.DString,'~') AS dsk1

CROSS APPLY

dbo.DelimitedSplit8K(tt.DString,'~') AS dsk2

WHERE

dsk1.ItemNumber = 1

AND dsk2.ItemNumber = 2

OUTPUT

IDDStringCodeItem

10111~Apple0111Apple

20222~Banana0222Banana

30333~Carrot0333Carrot

40444~Danish0444Danish

50555~Eclair0555Eclair

OR AS XML

<?xml version="1.0" ?>

<RESULTS1>

<RECORD>

<ID>1</ID>

<DString>0111~Apple</DString>

<Code>0111</Code>

<Item>Apple</Item>

</RECORD>

<RECORD>

<ID>2</ID>

<DString>0222~Banana</DString>

<Code>0222</Code>

<Item>Banana</Item>

</RECORD>

<RECORD>

<ID>3</ID>

<DString>0333~Carrot</DString>

<Code>0333</Code>

<Item>Carrot</Item>

</RECORD>

<RECORD>

<ID>4</ID>

<DString>0444~Danish</DString>

<Code>0444</Code>

<Item>Danish</Item>

</RECORD>

<RECORD>

<ID>5</ID>

<DString>0555~Eclair</DString>

<Code>0555</Code>

<Item>Eclair</Item>

</RECORD>

</RESULTS1>

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Steven Willis (5/7/2013)
AND dsk2.ItemNumber = 2

OP asked for the last (most right) part of the string, not for the second.

_____________
Code for TallyGenerator

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Sergiy (5/7/2013)
Steven Willis (5/7/2013)
AND dsk2.ItemNumber = 2
OP asked for the last (most right) part of the string, not for the second.

OP refers to "the tilde" in numerous places. One delimiter == two elements.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

No real need for dbo.DelimitedSplit8K here if you only expect maximum two parts (left and right). The following will work a bit faster (please note extended sample data):

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[DString] NVARCHAR(255) NULL,

PRIMARY KEY (ID))

INSERT INTO #TempTable

SELECT '0111~Apple' UNION ALL

SELECT '02222~Banana' UNION ALL

SELECT '033333~Carrot' UNION ALL

SELECT '044444~Danish' UNION ALL

SELECT '0555555~Eclair' UNION ALL

SELECT '000 NO TILDA' UNION ALL

SELECT NULL

SELECT

ID

,DString

,LEFT(DString, tld.ix - 1) AS

,SUBSTRING(DString, tld.ix + 1, 8000) AS

FROM

#TempTable AS tt

CROSS APPLY (SELECT NULLIF(CHARINDEX('~',tt.DString),0)) tld(ix)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sqldriver SSChampion Points: 10440 More actions · Answer 9

I picked up this parsename trick on here and have been loving it for ad hoc queries.

with data(col) as (

SELECT '0222~Banana' UNION ALL

SELECT '0333~Carrot' UNION ALL

SELECT '0444~Danish' UNION ALL

SELECT '0555~Eclair'

)

select parsename(replace(col, '~', '.'), 2) as 'Left',

parsename(replace(col, '~', '.'), 1) as 'Right'

from data

where col like '%~%'