Parameterized Expression: parse

Showing posts with label parse. Show all posts

Wednesday, March 28, 2012

Parsing Variable Length Delimited Records

I am running SQLServer 2000 to parse and store records in the EDIX12 format. This consists of variable length delimited records which I am passing to the "transforms" tab to process with VBScript.
The problem is though each segment has a defined number of fields, N, the standard states that if the final M fieds are empty/blank they are not to be sent. Thus, a segment defined to have 20 fields may have 6 the first time I see it, 13 the next time, etc. To access the columns in VBScript I use DTSSource("Col001"). This works as long as the columns are there, but gives an error when they are not. Is there a parameter telling me how many columns are defined? Or is there something akin to IFEXISTS("Colxxx") or exceptions?
How can I handle this situation? One suggestion has been to pass the entire segment to the Transforms section and break it up there.
Finally, what resources can yuo point me to for reference? I'd like to get good at using DTS since my client wants their project written for it.
Thanks for yuor help,
--greg

SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.

Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.

-Jamie

Parsing Variable Length Delimited Records

SSIS has built-in functionality for importing text files although that functionality doesn't handle variable number of columns too well.

Fear not though - the script component is your friend here. I highly recommend Donald Farmer's book which includes a chapter on importing text files using the script component.

-Jamie

Parsing Varchar2

I need to parse out some alphabetic characters from a Varchar2 field to leave only the number values. The field values look like this $40-35 or say $40$35.
Is there a sql function or statement to do this. Any help is greatly appreciated.
andavianselect to_number(substr(substr('$40-35' ,2),1,2)), to_number(substr(substr('$40-35' ,2),4,2)) from dual

is your select statement to parse out your 2 dollar amounts.

Originally posted by andavian
I need to parse out some alphabetic characters from a Varchar2 field to leave only the number values. The field values look like this $40-35 or say $40$35.

Is there a sql function or statement to do this. Any help is greatly appreciated.

andavian|||This will work only if the position of $ sign is fixed and the length of the string is fixed. Is it So?|||Yes, this is because you have given a fixed length variable and did not mention about how dynamic your VARCHAR would be. This assumes ur variable will be either ''$40-35" or "$40-35" which you have mentioned in your Question.
Originally posted by Rushi
This will work only if the position of $ sign is fixed and the length of the string is fixed. Is it So?

Parsing Text String Field - comma delimited

Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes? What I need is for each new value within the string to create a new record.

Example:

ID STRING

12345 1,2,3

67891 2,4

Becomes

12345 1

12345 2

12345 3

67891 2

67891 4

Thanks for any help!!

Check this page. It has great examples (if you are using 2005 there are two really great ways of handling this)

http://www.aspfaq.com/show.asp?id=2529

Parsing Text String Field

Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes?

Example:

ID STRING

12345 1,2,3

67891 2,4

Becomes

12345 1

12345 2

12345 3

67891 2

67891 4

Thanks for any help!!

You should be handling this in the Dataset not in SSRS.

If you need help with SQL statements to to do this I'm sure posting to the Transact-SQL forum will provide a response on how to get a dataset to come back with the relevant rows. You may have to use functions, sp's, or temporary tables to get the data back in that format.

Craig

Parsing Text String Field

Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes?

Example:

ID STRING

12345 1,2,3

67891 2,4

Becomes

12345 1

12345 2

12345 3

67891 2

67891 4

Thanks for any help!!

You should be handling this in the Dataset not in SSRS.

Craig

Parsing Text

Dear All,
I know how to parse this in ACCESS but not clear how to do this through
stored procedure. I Googled with different search criteria but didn't get an
y
useful hit. Any one have something handy?
I would like to parse:
"strFullName" listed as Habibullah, Mohammad I to three fields as:
strLastName: Habibullah
strFirstName: Mohammad
strMiddleName: I
Or another example, I would like "strFullName" listed as Huang, Chong Xi to
parse as:
strLastName: Huang
strFirstName: Chong
strMiddleName: Xi
Assuming that table name is User and field name is strFullName.
Thanks.
Habibullah.Habibullah,
You can use the T-SQL string functions to achieve your goal. See String
Functions in the SQL BOL. (esp. CHARINDEX and SUBSTRING). Ideally you'd
want to perform this string manipulation at the middle-tier or on the
client.
HTH
Jerry
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.|||Hi
One way to do it would be to use
SELECT
LEFT(strFullName,CHARINDEX(',',strFullNa
me)-1) AS strLastname,
LTRIM(SUBSTRING(strFullName,CHARINDEX(',
',strFullName)+1,LEN(strFullName)-CH
ARINDEX('
',REVERSE(strFullName))-CHARINDEX(',',strFullName))) AS strFirstname,
RIGHT(strFullName,CHARINDEX(' ',REVERSE(strFullName))-1) AS strMiddlename
FROM User
But this depends on how consistent the data is.
John
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.

Parse values from delimited string

Hi. I have a string like this which will be passed into a SQL Server 2000 stored procedure,

[15438|39][21347|96][24198|23]....

I need to take the values in the above string and insert them into a temp table so they will look like this. How can a delimited string be parsed into this? Thanks.

Exam ID Branch Number 15438 39 21347 96 24198 23

Look at the use of Jens' Split function, available here.

Seems like you need to split on the brackets, ][

and then again on the pipe, |

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||The Split function returns a table. How would I call the function if it needs to split the string twice? An example is very much appreciated. Also, I'm thinking of passing the data this way to make it even simpler,

|23415^33|23451^23|....
|||

I recommand to use the XML input rather than the delimted string. You already have a structured string. Just replace the string as follow as and utilize the OPENXML; Since your schema is simple it will be simple and faster. But String manipulation will hit your performance badly(bcs you have to do split of split of split).

Sample with your input...

Code Snippet

Declare @.Data as Varchar(100);

Declare @.XML as Varchar(8000);

Set @.Data = '[15438|39][21347|96][24198|23]'

Set @.XML = Replace(Replace(Replace(@.Data,'[','<row><col1>'),'|','</col1><col2>'),']','</col2></row>')

select @.XML = '<Data>' + @.XML + '</Data>'

Declare @.iDoc as Int;

Exec sp_xml_preparedocument @.iDoc OUTPUT, @.XML

Select * From OpenXML(@.iDoc, 'Data/row', 2) With (col1 int, col2 int)

Exec sp_xml_removedocument @.iDoc

You can pass the input from your server as follow as. (Advantage: You can pass text datatype from your UI to database, but delimted values wont allow more than 8000 chars)

Code Snippet

<Root>

<row>

</row>

<row>

</row>

<row>

</row>

</Root>

|||Since you can alter the input string format, I suggest that you use comma delimited, and follow Mani's suggestion about using XML.|||

Mani,

You're quite right. String manipulation is not good with T-SQL. (Unfortunately, shredding xml isn't so hot either...)

But in this case, it may be the lesser of the evils...

|||

Yes Arnie. Since the schema is simple OPENXML perform well.

In future if they want to migrate to SQL Server 2005, they can utilize the XQuery featuer. Where we can avoid the preparedocument.

|||

As a quick aside, did you verify the code you posted for the OP?

It didn't run for me...

|||Yes. All tags are got supressed. Corrected Now. Thank you.

|||You should fix the client or application sending the data to send it in a different format (xml, fixed length blob) or call the SP multiple times. You can also dump the rows into a temporary table and process the rows in the SP. Doing string manipulations is slow and kludgy.

Parse to first non-zero

Hello,
If I have a field that contains varchar type data which looks like '00312'
How can I parse the field to just display '312' ?
Thanks!
Patricedeclare @.i varchar(5)
select @.i ='00123'
select convert(int,@.i)
Convert it to an int
http://sqlservercode.blogspot.com/
"Patrice" wrote:

> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Patrice,
One way...try:
SELECT CAST(<COLUMN> AS INT) FROM <TABLE>
HTH
Jerry
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Another way, which is a bit safer, though not tremendously:
declare @.char10 char(10)
set @.char10 = '0000001234'
select substring(@.char10, patindex('%[^0a-z]%',@.char10), len(@.char10))
I just look for the first non zero (and I tossed in letter just for kicks)
character and substring from there. No conversion needed, so if you need to
look for
'00000012BC'
it will return:
12BC
instead of crashing out.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||In a tiered architecture, display is always done in the front end and
NEVER in the database. This more fundamental that just SQL; don't you
remember this from your first Software Engineering course?
The stinking dirty kludge is to CAST() the string to INTEGER. But
since you did not know that a column is not anything like field, you
probably have serious design problems in many place where you have

string and other data types.|||So to quote Bobby Boucher's mamma in the Waterboy, "CAST IS THE DEVIL!!!"
:-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
>

string and other data types.
>|||That was Kathy Bates! I had forgotten that Movie!
But I do remember my wife and I going to some movie years ago (forgot
the title) where George Cluney as an Army Officier and Nicole Kidman as
an atomic scientist trying to find an atomic bomb. When Kidman came on
the screen we both looked at each other and said "That [atomic bomb
expert] should have been Kathy Bates!"
But getting back from the flashback, the problem with CAST() is that
host languages do not all agree on how to handle SQL data types. Doing
the CAST() in SQL to feed it to an unknown host language conversion
adds overhead and makes results lesspredictable when someone looks at
the schema to figure out what happened. A version of the copy of a
copy of a copy.. problem.|||Yeah, I don't know why didn't think to say this. I got more interested in
just doing something other than cast. The word "display" should have jogged
something in my brain.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
>

string and other data types.
>

Parse the field

I have a table that has a "Problem" field with the following data
Customer Service...Jam...Jam 5...Planned
Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
New Drive
Sale Made
I need to separate this "Problem" field into separate fields the issue1,
issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
) .
What would be the easiest way to do so within the SELECT statement ?here's a quick and dirty way of parsing out the data:::
declare @.myString varchar(2000) --Set to max length of field
declare @.Issue1 varchar(100) --Set to max length of any single entry
set @.myString = 'Customer Service...Jam...Jam 5...Planned '
set @.myString = 'Technical
Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
--set @.myString = 'New Drive'
declare @.Index int -- Holds the next position of delimeter
declare @.delimiter char(1) -- holds delimeter character
set @.delimiter = '.' -- set the delimer value
set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
of delimeter
-- run a loop while we still have delimeters in the string
while @.Index>0
begin
-- get the next value
set @.Issue1 = substring(@.myString,1,@.Index-1)
-- do whatever you wish with the value print, save to table, array, etc)
print @.Issue1
-- parse the latest value from the current string
set @.myString = substring(@.myString,@.Index+3,len(@.myString))
-- get the next instance of the delimeter
set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
end
-- Get the last value from what's left
set @.Issue1 = ltrim(rtrim(@.myString))
-- do whatever you wish with the value print, save to table, array, etc)
print @.Issue1
Copy and paste it into QA and you can see how it works for you
Tony
"agenda9533" wrote:
> I have a table that has a "Problem" field with the following data
> Customer Service...Jam...Jam 5...Planned
> Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> New Drive
> Sale Made
> I need to separate this "Problem" field into separate fields the issue1,
> issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> ) .
> What would be the easiest way to do so within the SELECT statement ?|||This is awesome!
Only thing, Ijust don't know how to run/call this script within RS.
As far as I know RS only recognize T-Sql SELECT statements (in order to
create a dataset).
Am I wrong?
"Logicalman" wrote:
> here's a quick and dirty way of parsing out the data:::
>
> declare @.myString varchar(2000) --Set to max length of field
> declare @.Issue1 varchar(100) --Set to max length of any single entry
> set @.myString = 'Customer Service...Jam...Jam 5...Planned '
> set @.myString = 'Technical
> Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
> --set @.myString = 'New Drive'
> declare @.Index int -- Holds the next position of delimeter
> declare @.delimiter char(1) -- holds delimeter character
> set @.delimiter = '.' -- set the delimer value
> set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
> of delimeter
> -- run a loop while we still have delimeters in the string
> while @.Index>0
> begin
> -- get the next value
> set @.Issue1 = substring(@.myString,1,@.Index-1)
> -- do whatever you wish with the value print, save to table, array, etc)
> print @.Issue1
> -- parse the latest value from the current string
> set @.myString = substring(@.myString,@.Index+3,len(@.myString))
> -- get the next instance of the delimeter
> set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
> end
> -- Get the last value from what's left
> set @.Issue1 = ltrim(rtrim(@.myString))
> -- do whatever you wish with the value print, save to table, array, etc)
> print @.Issue1
>
> Copy and paste it into QA and you can see how it works for you
> Tony
> "agenda9533" wrote:
> > I have a table that has a "Problem" field with the following data
> >
> > Customer Service...Jam...Jam 5...Planned
> >
> > Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> > New Drive
> >
> > Sale Made
> >
> > I need to separate this "Problem" field into separate fields the issue1,
> > issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> > ) .
> >
> > What would be the easiest way to do so within the SELECT statement ?|||My first suggestion would be to place this in a Stored Procedure, and call
the SProc as the dataset for the RS report.
"agenda9533" wrote:
> This is awesome!
> Only thing, Ijust don't know how to run/call this script within RS.
> As far as I know RS only recognize T-Sql SELECT statements (in order to
> create a dataset).
> Am I wrong?
> "Logicalman" wrote:
> > here's a quick and dirty way of parsing out the data:::
> >
> >
> > declare @.myString varchar(2000) --Set to max length of field
> > declare @.Issue1 varchar(100) --Set to max length of any single entry
> >
> > set @.myString = 'Customer Service...Jam...Jam 5...Planned '
> > set @.myString = 'Technical
> > Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting '
> > --set @.myString = 'New Drive'
> >
> > declare @.Index int -- Holds the next position of delimeter
> > declare @.delimiter char(1) -- holds delimeter character
> > set @.delimiter = '.' -- set the delimer value
> >
> > set @.Index = patindex( '%'+@.delimiter+'%',@.myString) -- get first instance
> > of delimeter
> > -- run a loop while we still have delimeters in the string
> > while @.Index>0
> > begin
> > -- get the next value
> > set @.Issue1 = substring(@.myString,1,@.Index-1)
> > -- do whatever you wish with the value print, save to table, array, etc)
> > print @.Issue1
> > -- parse the latest value from the current string
> > set @.myString = substring(@.myString,@.Index+3,len(@.myString))
> > -- get the next instance of the delimeter
> > set @.Index = patindex( '%'+@.delimiter+'%',@.myString)
> >
> > end
> > -- Get the last value from what's left
> > set @.Issue1 = ltrim(rtrim(@.myString))
> > -- do whatever you wish with the value print, save to table, array, etc)
> > print @.Issue1
> >
> >
> > Copy and paste it into QA and you can see how it works for you
> >
> > Tony
> >
> > "agenda9533" wrote:
> >
> > > I have a table that has a "Problem" field with the following data
> > >
> > > Customer Service...Jam...Jam 5...Planned
> > >
> > > Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting
> > > New Drive
> > >
> > > Sale Made
> > >
> > > I need to separate this "Problem" field into separate fields the issue1,
> > > issue2, issue3, issue4 and issue5 (Customer Service, Jam, Jam 5,Planned
> > > ) .
> > >
> > > What would be the easiest way to do so within the SELECT statement ?

Parse sting with quotes in SQL

If, I have a string as follows , how Do I parse these quotes, such that it gives me 3 fields.

Eg of string is

Status changes "PASS" "FAIL" By "PW" '"/3/12/2007"

Resuls set should look like:

Column 1 Column 2 Column 3

PASS FAIL 3/12/2007

Pl advise.

Use a combination of the replace() function and Jen Suessmeyer's split function.

If there are no 'spaces' in the data, a single pass replacing the double quote with an empty string would work.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||Do you really need them in separate columns? If rows will do, then you could use Itzik's split function. Check insidetsql.com for the script.

Rob|||

this is a code to break a string "first"Second"third"Forth"Fifth"Sixth"" into First Second third forth etc however u have to pass and Extra " in the end of the string

declare @.Group varchar(8000)
Create table #Temp(GroupName varchar(100))
Declare @.Cnt int,@.CharLocation int,@.Previous int,@.Extent int
Set @.Group='"First"Second"Third"Forth"Fifth"Sixth""'
Set @.Cnt=1
Set @.Previous=1
Set @.Extent=1
While @.Cnt<>Len(@.Group)
BEgin
if substring(@.Group,@.Cnt,1)='"'
BEgin
--Select Substring(@.GroupName,@.Previous,@.Extent-1)
insert into #Temp values(Substring(@.Group,@.Previous,@.Extent-1))

sET @.Previous=@.Cnt+1
Set @.Extent=0
END
Set @.Extent=@.Extent+1
Set @.Cnt=@.Cnt+1
END
select * from #temp where GroupName <>''
drop table #Temp

try doing some manipulations and see if this code is of any help

RegarDs,

Jacx

|||I need this to be split into columns, the code above splits it into rows|||Jen's Split function I offered does split into columns -but of course you didn't try it, did you?|||

I did try Jens split function. I created that in the dbo, BUT it created that function as a type='TF', Not 'FN'.

So when I actually call the function, it gives err message invalid object.

This is how I call the Split Function .

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

and it errs out with the message below, though the split object does exist in the db. Any suggestions.

Thanks much.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Split'.

|||

You made a simple error. The function is a table valued function (TVF or "TF") That means that you use it like a table.

Instead of:

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Try:

SELECT * FROM dbo.Split( '"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Also, recall my earlier comment about needing to use the replace() function to remove the quotes and/or commas.|||

I have 2 issues:

1. I need the string split into multiple columns, not just 1. ie COL1 Shoud have "mod PEN" ,

Col2 should hv "approv" Col3 should hv "by wagety" and Col4 "3/21/2007"'. This split function returns everthying in 1 column.

2. Also, I have these values that are to be split, stored in a table. eg there is a table called notes, with a field called status.

That field status will have that records, which will have values such as "mod PEN" "approved" "by wagety" "3/21/2007"' this entire string will be 1 row. How can I use this function, for that tbl notes?

|||

1. I thought I understood that was your request.

Jen's split function will separte into different columns, if you are having difficulty, you may not have identified and be handling the delimiters correctly.

2. You have completely confused me now.

I previously understood that you wanted to separate

"PASS" "FAIL" By "PW" '"/3/12/2007"

into separate parts -at least that is what your example seemed to indicate that you wanted.

Now, it seems that you want to store all of the parts in one field called 'Status'.

Why not just store the current string in the Status field and be done?

|||

1. This is how I call Jens split function and it return all the values In 1 Single Column: eg

select * from dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

When you execute this, it returns the following , into 1 Column called SplitValue . I need the values between the doubel quotes, split into different columns, ie in this case 4 diff cols, ie Col1 should have mod PEN, Col2 should have approved, Colum # 3 shd have By wagety, Col 4 should have 3/21/2007. Can you pl assist

Output returns the foll:

OccurenceId SplitValue
-- --
1 "mod PEN" "approved" "by wagety" "3/21/2007"

Parse sting with quotes in SQL

If, I have a string as follows , how Do I parse these quotes, such that it gives me 3 fields.

Eg of string is

Status changes "PASS" "FAIL" By "PW" '"/3/12/2007"

Resuls set should look like:

Column 1 Column 2 Column 3

PASS FAIL 3/12/2007

Pl advise.

Use a combination of the replace() function and Jen Suessmeyer's split function.

If there are no 'spaces' in the data, a single pass replacing the double quote with an empty string would work.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||Do you really need them in separate columns? If rows will do, then you could use Itzik's split function. Check insidetsql.com for the script.

Rob|||

this is a code to break a string "first"Second"third"Forth"Fifth"Sixth"" into First Second third forth etc however u have to pass and Extra " in the end of the string

sET @.Previous=@.Cnt+1
Set @.Extent=0
END
Set @.Extent=@.Extent+1
Set @.Cnt=@.Cnt+1
END
select * from #temp where GroupName <>''
drop table #Temp

try doing some manipulations and see if this code is of any help

RegarDs,

Jacx

|||I need this to be split into columns, the code above splits it into rows|||Jen's Split function I offered does split into columns -but of course you didn't try it, did you?|||

I did try Jens split function. I created that in the dbo, BUT it created that function as a type='TF', Not 'FN'.

So when I actually call the function, it gives err message invalid object.

This is how I call the Split Function .

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

and it errs out with the message below, though the split object does exist in the db. Any suggestions.

Thanks much.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Split'.

|||

You made a simple error. The function is a table valued function (TVF or "TF") That means that you use it like a table.

Instead of:

select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Try:

SELECT * FROM dbo.Split( '"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Also, recall my earlier comment about needing to use the replace() function to remove the quotes and/or commas.|||

I have 2 issues:

1. I need the string split into multiple columns, not just 1. ie COL1 Shoud have "mod PEN" ,

Col2 should hv "approv" Col3 should hv "by wagety" and Col4 "3/21/2007"'. This split function returns everthying in 1 column.

2. Also, I have these values that are to be split, stored in a table. eg there is a table called notes, with a field called status.

|||

1. I thought I understood that was your request.

Jen's split function will separte into different columns, if you are having difficulty, you may not have identified and be handling the delimiters correctly.

2. You have completely confused me now.

I previously understood that you wanted to separate

"PASS" "FAIL" By "PW" '"/3/12/2007"

into separate parts -at least that is what your example seemed to indicate that you wanted.

Now, it seems that you want to store all of the parts in one field called 'Status'.

Why not just store the current string in the Status field and be done?

|||

1. This is how I call Jens split function and it return all the values In 1 Single Column: eg

select * from dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')

Output returns the foll:

OccurenceId SplitValue
-- --
1 "mod PEN" "approved" "by wagety" "3/21/2007"

Parse SQL Transaction log in .NET

I want to parse transaction log file.

Actually, i need to trace out changes in my database.[either by insertion/updation/deletion of data or adding/modifiying object.] within my application.

I study SQL Profile. But it is limited for its run and also resource hunger or extra burden on server in case of large size database and busy server.

So please guide me. Also suggest be its appropriate solution.

Thanks.

You could create internal trace in SQL Profiler format for SQL Server. You could do it using sp:

sp_trace_create/sp_trace_setvent

|||

May you guide little bit more or refer me any link for further guide... either parsing to transaction log or using any other mean.

Thansk

Jhan Zaib

|||Hi,

http://www.databasejournal.com/features/mssql/article.php/10894_2239461_2

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Parse SQL Statements

Hi.

Does anyone come across this error message whenever u try to parse ANY SQL statements in Management Studio 2005 (even parsing SELECT GETDATE() fails)...

>>>

.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
>>>

Thks!

See this KB article Yogi, I'm assuming it's the problem you're seeing:

http://support.microsoft.com/kb/910416

HTH

Parse SQL statement for list of tables

Hello,
I would like to make a procedure of sorts that accepts as input a full
sql statment and then is able to return a list (or print) of only the tables
referenced in the sql statement. Is this kind of code available?
Thanks.
Bentweak this
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO
Create table #tempTables (SplitString varchar(50))
DECLARE @.chvGroupNumbers VARCHAR(1000)
SELECT @.chvGroupNumbers ='select * from authors join publishers on bla bla
bla...'
insert into #tempTables
SELECT SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID + 1,
CHARINDEX(' ', ' ' + @.chvGroupNumbers + ' ', NumberID + 1) - NumberID -1)AS
Value
FROM NumberPivot
WHERE NumberID <= LEN(' ' + @.chvGroupNumbers + ' ') - 1
AND SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID, 1) = ' '
GO
select * from #tempTables where Splitstring in (SELECT table_name FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE')
Use Pubs for this example
http://sqlservercode.blogspot.com/
"Ben" wrote:

> Hello,
> I would like to make a procedure of sorts that accepts as input a full
> sql statment and then is able to return a list (or print) of only the tabl
es
> referenced in the sql statement. Is this kind of code available?
> Thanks.
> Ben

Parse Return value of SYSTEM_USER

DECLARE @.UserName nvarchar(100)
SELECT @.UserName = SYSTEM_USER

value returned is "Domain\NTSignonName"
What I want is only "NTSignonName"

Is there a function to do this or an easy parse for this in SQL2000?

lqI know I can do:

DECLARE @.UserNameWithDomain nvarchar(100)
SELECT @.UserNameWithDomain = SYSTEM_USER

DECLARE @.UserNameNoDomain nvarchar(100)
SELECT @.UserNameNoDomain =
SUBSTRING(@.UserNameWithDomain,CHARINDEX('\',@.UserN ameWithDomain)+1,100)

I'm hoping for something more aesthetic.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I know I can do:
> DECLARE @.UserNameWithDomain nvarchar(100)
> SELECT @.UserNameWithDomain = SYSTEM_USER
> DECLARE @.UserNameNoDomain nvarchar(100)
> SELECT @.UserNameNoDomain =
> SUBSTRING(@.UserNameWithDomain,CHARINDEX('\',@.UserN ameWithDomain)+1,100)
> I'm hoping for something more aesthetic.

I don't know if it's more esthetic, but parsename() is an alternative.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red|||is there any other way that i can make it to work
srinivas
"oj" wrote:
> "Parseonly" does not parse for dynamic query. This is by design. Basically,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >i am trying to write a stored procedure which parses the string query
> >passed
> > as input and returns whether it is a valid statement or not
> >
> > was trying to use "SET PARSEONLY ON" without any luck
> >
> > thanks
> > red
>
>|||No.
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> > is there any other way that i can make it to work
> >
> > srinivas
> >
> > "oj" wrote:
> >
> >> "Parseonly" does not parse for dynamic query. This is by design.
> >> Basically,
> >> 'parseonly' only parses for syntax and dynamic query is parsed at
> >> runtime.
> >>
> >> -- this would parse fine
> >> -- because @.sql is a valid variable
> >> -- and exec(@.sql) syntactically correct
> >> -- though this would err at runtime
> >> set parseonly on
> >> go
> >> declare @.sql sysname
> >> set @.sql='aflasfasfaslfsaf'
> >> exec(@.sql)
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >> >i am trying to write a stored procedure which parses the string query
> >> >passed
> >> > as input and returns whether it is a valid statement or not
> >> >
> >> > was trying to use "SET PARSEONLY ON" without any luck
> >> >
> >> > thanks
> >> > red
> >>
> >>
> >>
>
>|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
--
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> is there any other way that i can make it to work
>> srinivas
>> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>
>|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>

parse query

Parse A column containing SYntax

Has anyone tried to parse a column containing sql syntax to obtain the actual column names used in the syntax field. So if the field had acctno =1001 then it would return acctno.http://www.dbforums.com/showthread.php?t=1196943|||I haven't seen a script to do this, and it would be quite a challenge.

Parse a character seperated list of items

Hey,
Just spent WAY to long trying to parse out a item from a comma
seperated list without using temp tables. I bet there is something out
there already, but I couldn't find it. Here's my code for anyone that
wants it. The function parameters are the list of items, the seperator
you are looking for and the index of the item you want. NOTE: The list
is one based. If you enter 0 for the first item it will return NULL. If
the index higher than the number of items, the function also returns 0.
I'm sure the code could be better but I don't have any more time. If
someone improves the code, please post the new stuff so I can have a
look.

Cheers
Russ
CREATE FUNCTION get_list_item
(
@.string_list VARCHAR(8000),
@.seperator CHAR(1) = ',',
@.index_number INT = 1
)
RETURNS VARCHAR(1000)
AS
BEGIN
IF(@.index_number IS NULL)
BEGIN
SET @.index_number = 1
END
DECLARE @.seperator_index INT
DECLARE @.start_position INT
DECLARE @.next_index INT
DECLARE @.item_count INT
DECLARE @.return_item VARCHAR(1000)
DECLARE @.error_occured BIT
SET @.error_occured = 0
SET @.start_position = 0
SET @.item_count = 0
SET @.seperator_index = CHARINDEX(@.seperator, @.string_list)
IF(@.seperator_index > 0)
BEGIN
SET @.item_count = @.item_count + 1
--print 'COUNT = ' + CAST(@.item_count AS VARCHAR) + ' START: ' +
CAST(@.start_position AS VARCHAR) +
--' - END: ' + CAST(@.seperator_index AS VARCHAR)
WHILE(@.item_count < @.index_number)
BEGIN
SET @.start_position = @.seperator_index + 1
SET @.next_index = CHARINDEX(@.seperator, @.string_list,
@.start_position)
IF(@.next_index > 0)
BEGIN
SET @.seperator_index = @.next_index
SET @.item_count = @.item_count + 1
END
ELSE
BEGIN
--RAISERROR('The index requested was greater than the number of
items in the list', 16, 1)
--SET @.error_occured = 1
BREAK
END
--print 'COUNT = ' + CAST(@.item_count AS VARCHAR) + ' START: ' +
CAST(@.start_position AS VARCHAR) +
--' - END: ' + CAST(@.seperator_index AS VARCHAR)
END
IF(@.item_count = @.index_number - 1)
BEGIN
SET @.return_item = SUBSTRING(@.string_list,
@.start_position, (LEN(@.string_list) - @.start_position) + 1)
END
ELSE
BEGIN
IF(@.item_count = @.index_number)
BEGIN
SET @.return_item = SUBSTRING(@.string_list,
@.start_position, @.seperator_index - @.start_position)
END
END
END
ELSE
BEGIN
--DECLARE @.error_message VARCHAR(250)
--SET @.error_message = 'Seperator "' + @.seperator + '" not found.'
--RAISERROR(@.error_message,16,1)
SET @.return_item = NULL
END
RETURN @.return_item
END
P.S. This was tested with SQL Server 2000.
Russ
|||(russ.haley@.gmail.com) writes:
> Just spent WAY to long trying to parse out a item from a comma
> seperated list without using temp tables. I bet there is something out
> there already, but I couldn't find it.
Have a look at http://www.sommarskog.se/arrays-in-sql.html, and
particularly the core function for "Using a Table of Number". Further
down the page, there is also an example with fixed-length input.
I'm not really sure that I see the point with your function. You avoid
creating a table, but since a table is faster for lookup than a list,
I can't see any significant gain with it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||On 20 Mar 2006 12:07:45 -0800, russ.haley@.gmail.com wrote:

>Hey,
>Just spent WAY to long trying to parse out a item from a comma
>seperated list without using temp tables. I bet there is something out
>there already, but I couldn't find it.
Hi Russ,
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP
|||Hi, some time ago I wrote a function for that:
CREATE FUNCTION dbo.Split
(
@.String VARCHAR(200),
@.Delimiter VARCHAR(5)
)
RETURNS @.SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @.SplitLength INT
WHILE LEN(@.String) > 0
BEGIN
SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN
LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)
INSERT INTO @.SplittedValues
SELECT SUBSTRING(@.String,1,@.SplitLength)
SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)
END
RETURN
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||The point was I couldn't find an easy way to parse a character
seperated list. There are times in an application I am working on where
we get such data that is not parsed on the application side. Look
forward to reading the article you suggested.
Russ
|||Wow, very cool function. (I'm such a geek for saying that!) Thanks for
the feedback.
Russ

Wednesday, March 28, 2012

Parsing Variable Length Delimited Records

Parsing Variable Length Delimited Records

Parsing Varchar2

Parsing Text String Field - comma delimited

Parsing Text String Field

Parsing Text String Field

Parsing Text

Parse values from delimited string

Parse to first non-zero

Parse the field

Parse sting with quotes in SQL

Parse sting with quotes in SQL

Parse SQL Transaction log in .NET

Parse SQL Statements

Parse SQL statement for list of tables

Parse Return value of SYSTEM_USER

parse query

parse query

Parse A column containing SYntax

Parse a character seperated list of items

Parameterized Expression

Blog Archive

About Me