About Pricing Community Teams Start Free Trial

Lawrence Salvucci

asked on 11/28/2018

Parse Data in a SQL View

I am trying to parse data between certain characters but not sure how to go about this in a SQL view. The values I am trying to parse out are all numeric. They are wrapped in between a dash on each side. But there are also other numeric values in the data that are wrapped in between dashes so I want to make sure I grab the right section. The section I am looking to grab has 2 letters and a dash before it and then a dash after it. There are also other numeric values after the trailing dash but I don't want that section. Here are a few examples:

From this example I want to grab the 0.2487 440FSE-RD-0.2487-.0004X36
From this example I want to grab the 0.065 TI-GR2-TU-0.065-.0020

The 2 letters before the first dash aren't always the same but it will always be just 2 letters. Then they're followed by a dash. Then the numeric portion that I want follows that dash. And then it ends with another dash and then either more numeric characters or letters after that trailing dash. So the beginning will always start with 2 letters and then a dash. And the ending will always end with a dash. So how can I write this in my SQL view to grab this section?

Microsoft SQL ServerSQL

Last Comment

Scott Pletcher

8/22/2022 - Mon

Kyle Abrahams

11/28/2018

For this I always use a split function:

select * from fn_txt_split (col, '-')

Create function (not mine, just one I found):

/****** Object:  UserDefinedFunction [dbo].[fn_Txt_Split]    Script Date: 11/28/2018 1:12:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(8000) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(8000)) 
as begin 
DECLARE @Item Varchar(8000) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 



GO

Open in new window

Lawrence Salvucci

11/28/2018

ASKER

It's not just dashes because there could be other areas that have just dashes around it. It needs to be 2 letters and a dash to the left of the string I'm looking to parse, and then a dash to the right of this string. And I'm not that great with working with SQL so I wouldn't even know how to incorporate your code into a SQL view.

Scott Pletcher

11/28/2018

SELECT string, SUBSTRING(string, starting_byte, length) AS numeric_value
FROM ( VALUES('440FSE-RD-0.2487-.0004X36'),('TI-GR2-TU-0.065-.0020') ) AS test_data(string)
CROSS APPLY (
SELECT PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', string + '-') + 4 AS starting_byte
) AS ca1
CROSS APPLY (
SELECT CHARINDEX('-', SUBSTRING(string + '-', starting_byte, 100)) - 1 AS length
) AS ca2

Btw, the " + '-' " is there just in case somehow there is no closing dash after the number.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

D B

11/28/2018

Scott, I think one of his requirements is that there is a closing '-'. "And then it ends with another dash and then either more numeric characters or letters after that trailing dash."

PortletPaul

11/28/2018

Scott is referring to the following within his (effective!) solution:

PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', string + '-') + 4 AS starting_byte

Adding the dash to the data make sure that when searching for that character, the result will be > 0
This is useful if a subsequent function needs a length > 0

--example
select
     left(string,charindex('-',string) )      A
   , left(string,charindex('-',string+'-') ) B
FROM ( VALUES('some-text'),('some text') ) AS test_data(string)


+-------+-----------+
|   A   |     B     |
+-------+-----------+
| some- | some-     |
|       | some text |
+-------+-----------+

Open in new window

D B

11/28/2018

My concern was that if the specs state there will always be a terminating '-', then forcing one when one is not present may be forcing and invalid condition to become valid. If '473FE-DR-0.684' is to be considered invalid data, Scott's solution will make it valid. If that is okay with the author then there is no issue.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Lawrence Salvucci

11/29/2018

ASKER

Being very new at SQL and how to create views I'm not sure how to go about adding your answer to my view. Below is the SQL View that I am creating. The dbo.joitem.fdesc is the field that I need to parse.

SELECT        dbo.joitem.fjobno, dbo.joitem.fpartno, dbo.joitem.fdesc, dbo.jomast.fstatus
FROM            dbo.joitem INNER JOIN
                         dbo.jomast ON dbo.joitem.fjobno = dbo.jomast.fjobno
WHERE        (dbo.jomast.fstatus = 'RELEASED')

Open in new window

Scott Pletcher

11/29/2018

SELECT ji.fjobno, ji.fpartno, ji.fdesc, jm.fstatus,
SUBSTRING(ji.fdesc, ca1.starting_byte, ca2.length) AS numeric_value --<<-- chg col name to any name you want
FROM dbo.joitem AS ji INNER JOIN
dbo.jomast AS jm ON ji.fjobno = jm.fjobno
CROSS APPLY (
SELECT PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', ji.fdesc + '-') + 4 AS starting_byte
) AS ca1
CROSS APPLY (
SELECT CHARINDEX('-', SUBji.fdesc(ji.fdesc + '-', starting_byte, 100)) - 1 AS length
) AS ca2
WHERE (jm.fstatus = 'RELEASED')

Lawrence Salvucci

11/29/2018

ASKER

I get a SQL Execution Error when I try to execute the view. It says "The data types text and varchar are incompatible in the add operator."

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Scott Pletcher

11/29/2018

Oh, so column "fdesc" is of "text" data type?

Then we should convert it to varchar anyway before scanning it:

SELECT ji.fjobno, ji.fpartno, ji.fdesc, jm.fstatus,
SUBSTRING(CAST(ji.fdesc AS varchar(max)), ca1.starting_byte, ca2.length) AS numeric_value --<<-- chg col name to any name you want
FROM dbo.joitem AS ji INNER JOIN
dbo.jomast AS jm ON ji.fjobno = jm.fjobno
CROSS APPLY (
SELECT PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', CAST(ji.fdesc AS varchar(max)) + '-') + 4 AS starting_byte
) AS ca1
CROSS APPLY (
SELECT CHARINDEX('-', ji.fdesc(CAST(ji.fdesc AS varchar(max)) + '-', starting_byte, 100)) - 1 AS length
) AS ca2
WHERE (jm.fstatus = 'RELEASED')

Lawrence Salvucci

11/29/2018

ASKER

Yes it's a test data type. my apologies for not pointing that out earlier. I just pasted the new code and tried to run it but now it says: "Cannot find either column "SUBji" or the user-defined function or aggregate "SUBji.fdesc", or the name is ambiguous.

Scott Pletcher

11/29/2018

Very sorry, cut and paste error: it should just be "ji.fdesc", i.e., the table alias and the column name.

I've underlined and bolded where I corrected the error in the code above.

The text is not really a problem, we just to handle it carefully to make sure the CHARINDEX functions and other things work correcting on the data that comes from it. Native text data is difficult to use in SQL Server.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Lawrence Salvucci

11/29/2018

ASKER

No worries. It happens to all of us from time to time. I tried re-running the view but I get the same error but now it's "ji.fdesc".

"Cannot find either column "ji" or the user-defined function or aggregate "ji.fdesc", or the name is ambiguous."

Scott Pletcher

11/29/2018

D'OH, sorry, yeah, that should be SUBSTRING ...

SELECT ji.fjobno, ji.fpartno, ji.fdesc, jm.fstatus,
SUBSTRING(CAST(ji.fdesc AS varchar(max)), ca1.starting_byte, ca2.length) AS numeric_value --<<-- chg col name to any name you want
FROM dbo.joitem AS ji INNER JOIN
dbo.jomast AS jm ON ji.fjobno = jm.fjobno
CROSS APPLY (
SELECT PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', CAST(ji.fdesc AS varchar(max)) + '-') + 4 AS starting_byte
) AS ca1
CROSS APPLY (
SELECT CHARINDEX('-', SUBSTRING(CAST(ji.fdesc AS varchar(max)) + '-', starting_byte, 100)) - 1 AS length
) AS ca2
WHERE (jm.fstatus = 'RELEASED')

lol, I know what happened: I used "string" as column name in my test code, and when I did a global replace of "string" with your actual column name, SUBSTRING got changed to "SUBji.fdesc".

Lawrence Salvucci

11/30/2018

ASKER

It works but I also get an error once it loads the data. It says "Invalid length parameter passed to the LEFT or SUBSTRING function. What does that error mean?

I also noticed that some of the strings that I am trying to parse don't end with a "-". Some end with letters. I didn't realize there were any that would end with anything other than a dash. Also, can this be modified to find either a dash (like it does now), or a letter to know where the numeric value ends?

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Scott Pletcher

11/30/2018

Easy enough to use any non-numeric char (dash, letter, anything other than 0-9 or .(decimal)), as below.

As for the "invalid length parameter", sample data would be needed. There's just too many possibilities to try to come up with an answer out of thin air.

Edit: Usually if you're returning results, you'll see results up to the bad row, with the invalid length param. So just pull the next few rows and at least one of them should have the error, then I can review it.

select ji.fdesc,
SUBSTRING(CAST(ji.fdesc AS varchar(max)), ca1.starting_byte, ca2.length) AS numeric_value
from (
values('440FSE-RD-0.2487-.0004X36'),('TI-GR2-TU-0.065-.0020'),
('TI-GR2-TU-0.065A.0020') --added a row that used 'A' as the end rather than '-'
) as ji(fdesc)
CROSS APPLY (
SELECT PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', CAST(ji.fdesc AS varchar(max)) + '-') + 4 AS starting_byte
) AS ca1
CROSS APPLY (
SELECT PATINDEX('%[^0-9.]%', SUBSTRING(CAST(ji.fdesc AS varchar(max)) + '-', starting_byte, 100)) - 1 AS length
) AS ca2

Lawrence Salvucci

12/3/2018

ASKER

I ran the view and the error popped up about the invalid length parameter when it loaded down to row 256. Then I clicked OK to proceed and it loaded a total of 293 rows. I attached them in a spreadsheet and highlighted row 256 but I can't see why that would throw the error.

Here is the view that I am using:

SELECT        ji.fjobno, ji.fpartno, ji.fdesc, jm.fstatus, SUBSTRING(CAST(ji.fdesc AS varchar(max)), ca1.starting_byte, ca2.length) 
                         AS numeric_value
/*<<-- chg col name to any name you want*/ FROM dbo.joitem AS ji INNER JOIN
                         dbo.jomast AS jm ON ji.fjobno = jm.fjobno CROSS APPLY (
                                SELECT PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', CAST(ji.fdesc AS varchar(max)) + '-') + 4 AS starting_byte 
                           ) AS ca1
                           CROSS APPLY (
                                SELECT PATINDEX('%[^0-9.]%', SUBSTRING(CAST(ji.fdesc AS varchar(max)) + '-', starting_byte, 100)) - 1 AS length
                           ) AS ca2
WHERE        (jm.fstatus = 'RELEASED') AND ji.fprodcl <> '03'

Open in new window

Sample-Data.xlsx

Scott Pletcher

12/4/2018

Thanks for the data. I'll follow up as soon as I can. Extremely busy right now.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Scott Pletcher

12/4/2018

I loaded that data into a table and ran the CROSS APPLY code against it. I didn't get any errors.

I assumed you won't have a value longer than 100 bytes. Just to be safe, if you want to, we can increase that to 200 bytes (or whatever).

SELECT ji.fjobno, ji.fpartno, ji.fdesc, jm.fstatus, SUBSTRING(CAST(ji.fdesc AS varchar(max)), ca1.starting_byte, ca2.length)
AS numeric_value
/*<<-- chg col name to any name you want*/ FROM dbo.joitem AS ji INNER JOIN
dbo.jomast AS jm ON ji.fjobno = jm.fjobno CROSS APPLY (
SELECT PATINDEX('%[-][A-Z][A-Z][-][0-9]%[-]%', CAST(ji.fdesc AS varchar(max)) + '-') + 4 AS starting_byte
) AS ca1
CROSS APPLY (
SELECT PATINDEX('%[^0-9.]%', SUBSTRING(CAST(ji.fdesc AS varchar(max)) + '-', starting_byte, 200)) - 1 AS length
) AS ca2
WHERE (jm.fstatus = 'RELEASED') AND ji.fprodcl <> '03'

Lawrence Salvucci

12/5/2018

ASKER

I tried changing it to 200 and even higher but I'm still getting the error "Invalid length parameter passed to the left or substring function". It works after I click OK on that error. Is there any way to suppress the error?

Scott Pletcher

12/5/2018

Not directly.

But, aah, I think I see a potential problem now This expression:

SELECT PATINDEX('%[^0-9.]%', SUBSTRING(CAST(ji.fdesc AS varchar(max)) + '-', starting_byte, 200)) - 1 AS length

could maybe return -1 if fdesc is an empty string -- will check back later, busy now.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

ASKER CERTIFIED SOLUTION

Scott Pletcher

12/5/2018

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Lawrence Salvucci

12/5/2018

ASKER

BOOM! That fixed it! Now it's working without any errors! I cannot thank you enough for all your help, Scott! I truly appreciate all your help with this!

Scott Pletcher

12/5/2018

You're welcome! Glad I eventually got to the right solution :-).

Certified Expert Program

Credly Partnership

Udemy Partnership