Data between set delimited fields

Question

Data between set delimited fields

PaulCNY

SSC Rookie

Points: 47
More actions
December 1, 2008 at 10:42 am

#196752

Hi everyone,
Just found this forum and found a lot of useful information. I was perusing some articles relevant to my problem and I think I might be close, but not sure yet. Here's what I'm trying to do:
I have data for a report that is not currently displaying correctly because there is a substring that is pulling in the value after the 30th space and taking the next 11 characters. However, the field that I want to pull in varies in length. Here's an example:
My information: Member Details: Member Number 12345678: Status OK
My information: Member Details: Member Number 123456: Status OK
The information I want to pull in is the numeric value for the member number. I can set the substring to look at the beginning of the number, but I can't seem to figure out how to only pull in the numbers from these fields without getting these results:
12345678
123456 :
Is there a way I can either strip the semi-colon and space if it exists?
Here's what I've been using, the field that has the information I'm seeking is the errormessage field:
SELECT memberid,
LTRIM(RTRIM(SUBSTRING(
/* */ errormessage,
/* */ CHARINDEX(':',errormessage,1) + 29,
/* */ CHARINDEX(':',
SUBSTRING(errormessage, CHARINDEX(':', errormessage, 1) + 1, 10) + ':') - 1
) ) ) AS memberid
FROM #test
It's pulling in the information I want basically, but it's displaying the semi colon on the member numbers with a shorter length. Is there an easier way to do this where I can pull data from the second semi-colon on?
I would appreciate any help. I'm still a newbie at this and trying to get my feet more wet!
Thanks!
Paul

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

I'm including some code below;

there is a table called "tally" that is required; Using a tally/numbers table to do string manipulations has proven to be the fastest way to manipulate data in a vast number of ways.

the function is simple on the face of it, every char is evaluated, and rejected if not 0-9.

you might tweak it a bit if you needed to include minus signs, periods for decimals, etc. but this will get you started.

much of this was stolen shamelessly from a Jeff Moden example.

[font="Courier New"]

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM MASTER.dbo.SysColumns sc1, MASTER.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

GO

CREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

CASE

--ascii numbers are 48(for '0') thru 57 (for '9')

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57

THEN SUBSTRING(@OriginalText,Tally.N,1)

ELSE ''

END

FROM dbo.Tally WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END

GO

SELECT dbo.StripNonNumeric('123456 :')

SELECT dbo.StripNonNumeric(Fieldname),OtherCols, FROM sometable[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulCNY SSC Rookie Points: 47 More actions · Answer 2

Lowell,

Thanks for the suggestion!

What I forgot to mention was that these member "numbers" are alphanumeric and will always have a prefix containing two letters of some sort. Can this work within the code as well? Also, I'm assuming it's safe to drop this table after every time this process is run? (The number of records could grow quite large over time).

--Paul

Lowell SSC Guru Points: 323504 More actions · Answer 3

i would highly recommend you have a permanent Tally table; As you read more here, you'll see how usefull it really is;

on SQL2000, you need a table that is at elast 8000 rows , so you can manipulate the largest varchar(), on my 2005, i make sure it has a million rows.

the space it uses is minimal, and the benefits are outragous once you start using it.

you could easily grab prtions of your field, ie SELECT LEFT(yourField,2) + dbo.StripNonNumeric(SUBSTRING(yourField,2,30) ) so you keep the first part, but strip everything after the second character; the 30 length should be the max() varcharsize of the field or more;

PaulCNY (12/1/2008)
Lowell,
Thanks for the suggestion!
What I forgot to mention was that these member "numbers" are alphanumeric and will always have a prefix containing two letters of some sort. Can this work within the code as well? Also, I'm assuming it's safe to drop this table after every time this process is run? (The number of records could grow quite large over time).
--Paul

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

I'm probably the world's biggest fan of the Tally table... keep that in mind when I say that Tally table solutions are probably not appropriate nor as fast as a more direct solution when trying to extact data from "tagged" information rows like the one's you show. The following should beat the pants off a Tally table solution and gives you the added benefit of the "tag" (Member Number xxxxx: ) showing up anywhere on the line and still being captured correctly.

--===== Create a test table and populate it

-- (This is NOT part of the solution)

DECLARE @StringTest TABLE (BigString VARCHAR(200))

INSERT INTO @StringTest (BigString)

SELECT 'My information: Member Details: Member Number 12345678: Status OK' UNION ALL

SELECT 'My information: Member Details: Member Number 123456: Status OK' UNION ALL

SELECT 'Member Number 123: Status OK My information: Member Details: '

--===== Demonstrate the "tagged" split.

SELECT LEFT(split.FirstSplit,CHARINDEX(':',split.FirstSplit)-1) AS MemberNumber

FROM (SELECT SUBSTRING(BigString,CHARINDEX('Member Number ',BigString)+14,200) AS FirstSplit

FROM @StringTest) split

Since this is SQL Servr 2005, someone may be able to come up with a solution using the new "XML" code, but the above code works fine in both SQL Server 2000 and 2005. Unless they really beat the dog in 2008, it should work just fine there, too.

--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)

PaulCNY SSC Rookie Points: 47 More actions · Answer 5

Jeff Moden (12/1/2008)
I'm probably the world's biggest fan of the Tally table... keep that in mind when I say that Tally table solutions are probably not appropriate nor as fast as a more direct solution when trying to extact data from "tagged" information rows like the one's you show. The following should beat the pants off a Tally table solution and gives you the added benefit of the "tag" (Member Number xxxxx: ) showing up anywhere on the line and still being captured correctly.
--===== Create a test table and populate it
-- (This is NOT part of the solution)
DECLARE @StringTest TABLE (BigString VARCHAR(200))
INSERT INTO @StringTest (BigString)
SELECT 'My information: Member Details: Member Number 12345678: Status OK' UNION ALL
SELECT 'My information: Member Details: Member Number 123456: Status OK' UNION ALL
SELECT 'Member Number 123: Status OK My information: Member Details: '
--===== Demonstrate the "tagged" split.
SELECT LEFT(split.FirstSplit,CHARINDEX(':',split.FirstSplit)-1) AS MemberNumber
FROM (SELECT SUBSTRING(BigString,CHARINDEX('Member Number ',BigString)+14,200) AS FirstSplit
FROM @StringTest) split
Since this is SQL Servr 2005, someone may be able to come up with a solution using the new "XML" code, but the above code works fine in both SQL Server 2000 and 2005. Unless they really beat the dog in 2008, it should work just fine there, too.

Jeff,

Thanks for this solution. I'm in the midst of seeing how I can make this work and have a couple of questions if you don't mind. The statements worked fine of course when I ran it in SQL Server 2000, but when I tried to tinker with it is where I ran into problems:

1) Would I need to do a union all necessarily? Is it just considered good housekeeping to do it or does it serve a distinct purpose within this statement?

2) The table has already been created and is named #final and the column name is 'errormessage' . The constant string that prefixes the actual member number I'm trying to extract reads like this:

"Server: 1234567 - Member Number: AAA1234567 : More Text with varying lengths depending on the error message"

What I would like to do is use the authid field in this same table to extract the member number from the errormessage table.

I know I've probably butchered this because I'm getting syntax errors but here's what I came up with:

update #final

SELECT LEFT(split.FirstSplit,CHARINDEX(':',split.FirstSplit)-1) AS authid

FROM (SELECT SUBSTRING(,CHARINDEX('Member Number:',errormessage)+6,200) AS FirstSplit

FROM #final) split

Can you show me the error of my ways?

Thanks for your help again,

Paul

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

1) Would I need to do a union all necessarily? Is it just considered good housekeeping to do it or does it serve a distinct purpose within this statement?

Heh... First, ya gotta read the comments in the code... 😉 the UNION ALL stuff has nothing to do with the solution and should be left out... It's just a test setup to demo the code I wrote so other folks don't have to do the same setup to play with the code...

--===== Create a test table and populate it

[font="Arial Black"]-- (This is NOT part of the solution)[/font]

2) The table has already been created and is named #final and the column name is 'errormessage' . The constant string that prefixes the actual member number I'm trying to extract reads like this:
"Server: 1234567 - Member Number: AAA1234567 : More Text with varying lengths depending on the error message"
What I would like to do is use the authid field in this same table to extract the member number from the errormessage table.
I know I've probably butchered this because I'm getting syntax errors but here's what I came up with:
update #final
SELECT LEFT(split.FirstSplit,CHARINDEX(':',split.FirstSplit)-1) AS authid
FROM (SELECT SUBSTRING(,CHARINDEX('Member Number:',errormessage)+6,200) AS FirstSplit
FROM #final) split

Sure... but what would be most helpful is if you posted a dozen or so rows of data in a readily "consumable" format like I did with the UNION ALL thing... see the link in my signature line on how to easily do such a thing. Not trying to be mean... trying to post a tested solution with actual data. 😀

--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)