SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parse a string help


Parse a string help

Author
Message
Jeffery Williams
Jeffery Williams
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5530 Visits: 1119
I have a string that I need to parse. The text will vary but the format always the same.

Example Text: Hello5-E-100

I need this parsed into two variables.

First being Hello5-E and the second simply 100


Can someone please assist with this? I have the following code that creates (HELLO5 and second as E100)

DECLARE @VendItemNumber varchar(100) = 'HELLO5-E-100'

-- HOTEL5-E 100

SELECT LTRIM(RTRIM(REPLACE(LEFT(@VendItemNumber, CHARINDEX('-', @VendItemNumber)),'-' ,'')))
,LTRIM(RTRIM(REPLACE(SUBSTRING(@VendItemNumber, CHARINDEX('-', @VendItemNumber), LEN(@VendItemNumber) - CHARINDEX('-', @VendItemNumber) + 1),'-' ,'')))

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Jeffery Williams
Jeffery Williams
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5530 Visits: 1119
A little Closer now just need to move the 'E' over and I will be set... HELP! lol


DECLARE @VendItemNumber varchar(100) = 'HELLO5-E-100'


-- HOTEL5-E 100

SELECT LTRIM(RTRIM(REPLACE(LEFT(@VendItemNumber, CHARINDEX('-', @VendItemNumber)),'-' ,'-')))
,LTRIM(RTRIM(REPLACE(SUBSTRING(@VendItemNumber, CHARINDEX('-', @VendItemNumber), LEN(@VendItemNumber) - CHARINDEX('-', @VendItemNumber) + 1),'-' ,'')))

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401389 Visits: 43424
Jeffery Williams - Tuesday, March 6, 2018 1:02 PM
A little Closer now just need to move the 'E' over and I will be set... HELP! lol


DECLARE @VendItemNumber varchar(100) = 'HELLO5-E-100'


-- HOTEL5-E 100

SELECT LTRIM(RTRIM(REPLACE(LEFT(@VendItemNumber, CHARINDEX('-', @VendItemNumber)),'-' ,'-')))
,LTRIM(RTRIM(REPLACE(SUBSTRING(@VendItemNumber, CHARINDEX('-', @VendItemNumber), LEN(@VendItemNumber) - CHARINDEX('-', @VendItemNumber) + 1),'-' ,'')))


Something like this?

DECLARE @VendItemNumber varchar(100) = 'HELLO5-E-100';

SELECT LEFT(@VendItemNumber,LEN(@VendItemNumber) - CHARINDEX('-',REVERSE(@VendItemNumber))), RIGHT(@VendItemNumber,CHARINDEX('-',REVERSE(@VendItemNumber)) - 1);



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeffery Williams
Jeffery Williams
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5530 Visits: 1119
Lynn Pettis - Tuesday, March 6, 2018 1:55 PM
Jeffery Williams - Tuesday, March 6, 2018 1:02 PM
A little Closer now just need to move the 'E' over and I will be set... HELP! lol


DECLARE @VendItemNumber varchar(100) = 'HELLO5-E-100'


-- HOTEL5-E 100

SELECT LTRIM(RTRIM(REPLACE(LEFT(@VendItemNumber, CHARINDEX('-', @VendItemNumber)),'-' ,'-')))
,LTRIM(RTRIM(REPLACE(SUBSTRING(@VendItemNumber, CHARINDEX('-', @VendItemNumber), LEN(@VendItemNumber) - CHARINDEX('-', @VendItemNumber) + 1),'-' ,'')))


Something like this?

DECLARE @VendItemNumber varchar(100) = 'HELLO5-E-100';

SELECT LEFT(@VendItemNumber,LEN(@VendItemNumber) - CHARINDEX('-',REVERSE(@VendItemNumber))), RIGHT(@VendItemNumber,CHARINDEX('-',REVERSE(@VendItemNumber)) - 1);


Thank you so much


<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401389 Visits: 43424

My pleasure.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55533 Visits: 9048
Some code formatting food for thought...
For cases where I need to repeat the same expression multiple times you can create on "on-the-fly" inline function to simplify your code. In Lynn's code this expression is repeated a couple times:
CHARINDEX('-',REVERSE(@VendItemNumber)


You can simplify the code using the Table Value constructor like this:
SELECT LEFT(f.vi,LEN(f.vi) - f.pos), RIGHT(f.vi, f.pos - 1) 
FROM (VALUES (CHARINDEX('-',REVERSE(@VendItemNumber)), @VendItemNumber)) f(pos,vi);


For situations with longer expressions that are called several times you will see much cleaner code and without any performance penalty. In fact, there are times where you will see a performance improvement in cases where the optimizer is not smart enough to realize that it only needs to calculate the same expression once.

There is no performance penalty for doing this, actually - there are times where you will see a performance improvement. For example, run this will "include actual execution plan" turned on:

-- sample data 
CREATE TABLE #sometable(someid int identity, somevalue decimal(10,2));
INSERT #sometable(somevalue) VALUES (100),(1050),(5006),(111),(4);
GO

DECLARE @var1 int = 100, @var2 int = 50, @var3 int = 900, @topPct tinyint = 90;
-- version 1 with repeated formula
SELECT TOP (@topPct) PERCENT
someid,
somevalue,
someCalculation =
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END,
someRank = dense_rank() OVER (ORDER BY
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END)
FROM #sometable
WHERE CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END
BETWEEN 900 AND 2000
ORDER BY -- simulate another event that causes a sort
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END;

-- version 2 with inline alias
SELECT TOP (@topPct) PERCENT
someid,
somevalue,
someCalculation = itvf.result,
someRank = dense_rank() OVER (ORDER BY itvf.result)
FROM #sometable
CROSS APPLY (VALUES(CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3
ELSE @var3+somevalue END)) itvf(result)
WHERE itvf.result between 900 and 2000
ORDER BY itvf.result;


-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)SSC Guru (401K reputation)

Group: General Forum Members
Points: 401389 Visits: 43424
Alan.B - Wednesday, March 7, 2018 9:11 AM
Some code formatting food for thought...
For cases where I need to repeat the same expression multiple times you can create on "on-the-fly" inline function to simplify your code. In Lynn's code this expression is repeated a couple times:
CHARINDEX('-',REVERSE(@VendItemNumber)


You can simplify the code using the Table Value constructor like this:
SELECT LEFT(f.vi,LEN(f.vi) - f.pos), RIGHT(f.vi, f.pos - 1) 
FROM (VALUES (CHARINDEX('-',REVERSE(@VendItemNumber)), @VendItemNumber)) f(pos,vi);


For situations with longer expressions that are called several times you will see much cleaner code and without any performance penalty. In fact, there are times where you will see a performance improvement in cases where the optimizer is not smart enough to realize that it only needs to calculate the same expression once.

There is no performance penalty for doing this, actually - there are times where you will see a performance improvement. For example, run this will "include actual execution plan" turned on:

-- sample data 
CREATE TABLE #sometable(someid int identity, somevalue decimal(10,2));
INSERT #sometable(somevalue) VALUES (100),(1050),(5006),(111),(4);
GO

DECLARE @var1 int = 100, @var2 int = 50, @var3 int = 900, @topPct tinyint = 90;
-- version 1 with repeated formula
SELECT TOP (@topPct) PERCENT
someid,
somevalue,
someCalculation =
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END,
someRank = dense_rank() OVER (ORDER BY
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END)
FROM #sometable
WHERE CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END
BETWEEN 900 AND 2000
ORDER BY -- simulate another event that causes a sort
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END;

-- version 2 with inline alias
SELECT TOP (@topPct) PERCENT
someid,
somevalue,
someCalculation = itvf.result,
someRank = dense_rank() OVER (ORDER BY itvf.result)
FROM #sometable
CROSS APPLY (VALUES(CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3
ELSE @var3+somevalue END)) itvf(result)
WHERE itvf.result between 900 and 2000
ORDER BY itvf.result;


Actually, thanks for showing this as it goes right along with the DRY principle. I just need to work harder and using it.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55533 Visits: 9048
Lynn Pettis - Wednesday, March 7, 2018 9:48 AM
Alan.B - Wednesday, March 7, 2018 9:11 AM
Some code formatting food for thought...
For cases where I need to repeat the same expression multiple times you can create on "on-the-fly" inline function to simplify your code. In Lynn's code this expression is repeated a couple times:
CHARINDEX('-',REVERSE(@VendItemNumber)


You can simplify the code using the Table Value constructor like this:
SELECT LEFT(f.vi,LEN(f.vi) - f.pos), RIGHT(f.vi, f.pos - 1) 
FROM (VALUES (CHARINDEX('-',REVERSE(@VendItemNumber)), @VendItemNumber)) f(pos,vi);


For situations with longer expressions that are called several times you will see much cleaner code and without any performance penalty. In fact, there are times where you will see a performance improvement in cases where the optimizer is not smart enough to realize that it only needs to calculate the same expression once.

There is no performance penalty for doing this, actually - there are times where you will see a performance improvement. For example, run this will "include actual execution plan" turned on:

-- sample data 
CREATE TABLE #sometable(someid int identity, somevalue decimal(10,2));
INSERT #sometable(somevalue) VALUES (100),(1050),(5006),(111),(4);
GO

DECLARE @var1 int = 100, @var2 int = 50, @var3 int = 900, @topPct tinyint = 90;
-- version 1 with repeated formula
SELECT TOP (@topPct) PERCENT
someid,
somevalue,
someCalculation =
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END,
someRank = dense_rank() OVER (ORDER BY
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END)
FROM #sometable
WHERE CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END
BETWEEN 900 AND 2000
ORDER BY -- simulate another event that causes a sort
CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END;

-- version 2 with inline alias
SELECT TOP (@topPct) PERCENT
someid,
somevalue,
someCalculation = itvf.result,
someRank = dense_rank() OVER (ORDER BY itvf.result)
FROM #sometable
CROSS APPLY (VALUES(CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3
ELSE @var3+somevalue END)) itvf(result)
WHERE itvf.result between 900 and 2000
ORDER BY itvf.result;


Actually, thanks for showing this as it goes right along with the DRY principle. I just need to work harder and using it.


BigGrin

-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

gvoshol 73146
gvoshol 73146
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2617 Visits: 1368
Jeffery Williams - Tuesday, March 6, 2018 12:59 PM
I have a string that I need to parse. The text will vary but the format always the same.

Example Text: Hello5-E-100

I need this parsed into two variables.

First being Hello5-E and the second simply 100

You said the format will always be the same.
Does that mean it will always be 6 a/n chars, a dash, a char, another dash, 3 numbers?
Or it will always be a string of varying length containing 2 dashes somewhere?
If the latter, simply using LEFT and RIGHT would suffice. But I suspect that's not the answer or you would have thought of it yourself - so see the other replies.

Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79445 Visits: 11487
If the format will always be delimited by a dash - then you can easily utilize a string split utility. A normal string split will split the results into separate rows - but a modified utility can be built to return a specific element from a string. Using something like that - this becomes:


Declare @testTable Table (InputString varchar(100));

Insert Into @testTable
Values ('HELLO5-E-100'), ('Hotel5-E-101'), ('BYE5-F-001');

Select concat(p1.Item, '-', p2.Item) As Part1
, p3.Item As Part2
From @testTable t
Cross Apply dbo.fnGetStringElement(t.InputString, '-', 1) As p1
Cross Apply dbo.fnGetStringElement(t.InputString, '-', 2) As p2
Cross Apply dbo.fnGetStringElement(t.InputString, '-', 3) As p3;


Here is the function...


Create Function [dbo].[fnGetStringElement] (
@pString varchar(8000)
, @pDelimiter char(1)
, @pElement int)
Returns Table
With Schemabinding
As
Return

With e1(n)
As ( --=== Create Ten 1s
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 --10
)
, e2(n) As (Select 1 From e1 a, e1 b) -- 100
, e3(n) As (Select 1 From e2 a, e2 b) -- 10,000
, cteTally (Number)
As (
Select Top (datalength(isnull(@pString, 0)))
row_number() over(Order By (Select Null))
From e3
)
, cteStart(n1)
As (
Select 1
Union All
Select t.Number + 1
From cteTally t
Where substring(@pString, t.Number, 1) = @pDelimiter
)
, cteEnd (n1, l1)
As (
Select s.n1
, coalesce(nullif(charindex(@pDelimiter, @pString, s.n1), 0) - s.n1, 8000)
From cteStart s
)
, cteSplit --==== Do the split
As (
Select row_number() over(Order By e.n1) As ItemNumber
, substring(@pString, e.n1, e.l1) As Item
From cteEnd e
)
Select ltrim(rtrim(Item)) As Item
From cteSplit
Where ItemNumber = @pElement;
Go


Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search