Need to parse text field
-
Hello all. I have a field with text data. I need to run a report and parse my text using a period as separator between statements. Somehting like
"Text.text2.text3.text4"
I need to create four different strings using the period as my separator to get (please note that my text between periods is not always the same!!)
text
text2
text3
text4
Any assitance will be really appreciated
-
Search for "Split" in the script section and you will get quite a few funtions.
Here in one that I have used
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=592
-
And don't forget about this handy little baby:
This example uses PARSENAME to return information about the authors table in the pubs database.
USE pubsSELECT PARSENAME('pubs..authors', 1) AS 'Object Name'SELECT PARSENAME('pubs..authors', 2) AS 'Owner Name'SELECT PARSENAME('pubs..authors', 3) AS 'Database Name'SELECT PARSENAME('pubs..authors', 4) AS 'Server Name'[font="Courier New"]ZenDada[/font]
-
Thank you for your input. parse name does not work because the values are not as concistent as we want to have and split does not work either. Even though split does what I needed, the developers needs something that can be called from native SQL. They have to be able to connect to business objects and this doesn't read, I was told, but ??? who knows, created function. They said only reads native SQL Server functions. Thank you anyway. Will keep researching.
-
>>Thank you for your input. parse name does not work because the values are not as concistent as we want to have
Ummm... post some real data and let us test it... If your data is ANY string containing the 4 periods, I'll just bet parse name can handle it.
--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) -
Sure, it reads something like this:
CEO.New Address Validated.Location: \\Main\e:\NewAddressValue.Last Update Jeff Morgan
Another one reads like this:
CSR_HelpDesk.Starttime: 9:30AM Midshift.Changed Supervisor HR request.New Sup Anna Holland.New Shift: 7:30 AM
OK those are just two. As you can see there is no consistency on the length of the data to do a right or left or len.
Thank you
-
forgot, the only sure thing is the period is the separator. Thank you
-
I guess that means you may have more or less than the 4 parts you mentioned in your first post, eh?
Ok... let's hope Business Objects has a cross-tab function (or not, depending on what you are doing) to put this data back together (if it doesn't, perhaps BO was a bad investment?). This does the split for a whole table (example uses your data) and provides lot's of extra info for the BO guys to fiddle with (although you'd think BO would have a Split function that could handle this)...
--===== IF test table exists, drop it
IF OBJECT_ID('dbo.JbmParseTest') IS NOT NULL
DROP TABLE dbo.JbmParseTest
--===== Create the test table and populate it
CREATE TABLE dbo.JbmParseTest (RowNum INT IDENTITY(1,1), MyString VARCHAR(200))
INSERT INTO dbo.JbmParseTest (MyString)
SELECT 'CEO.New Address Validated.Location: \\Main\e:\NewAddressValue.Last Update Jeff Morgan' UNION ALL
SELECT 'CSR_HelpDesk.Starttime: 9:30AM Midshift.Changed Supervisor HR request.New Sup Anna Holland.New Shift: 7:30 AM'
SELECT j.RowNum,
t.N AS StringStartPosition,
CHARINDEX('.', j.MyString, t.N + 1) - t.N - 1 AS StringLength,
LTRIM(SUBSTRING(j.MyString, t.N+1, CHARINDEX('.', j.MyString, t.N + 1) - t.N - 1)) AS ParsedString
FROM Tally t
INNER JOIN
(SELECT RowNum,'.'+MyString+'.' AS MyString FROM dbo.JbmParseTest) j
ON SUBSTRING(j.MyString, t.N, 1) = '.'
AND t.N < LEN(j.MyString)
Obviously, you'll need something unique to identify the unique rows to both the query and BO... I used an autonumbering RowNum column which could be any IDENTITY column... I suppose you could use a DATETIME column but that leaves the possibility of dupes which would confuse the heck out of the query and BO. A GUID column would also work.
Oh, yeah... I forget because using them just sorta comes natural to me... you'll need a "Tally" or Numbers" table to pull this one off... here's how to build it... you should keep it as a permanent table because it has lot's of uses to simplify complex stuff like this problem...
--===== Create and populate the Tally table on the fly
SELECT TOP 9999
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysComments sc1,
Master.dbo.SysComments sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--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) -
Ok... let's hope Business Objects has a cross-tab function If it doesn't, post back 'cause I have a fix for that, 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) -
Thank you. I will check this out as soon as I get back to the office tomorrow.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply