selecting data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

selecting data

Author

Topic

regelos
Starting Member

13 Posts

Posted - 2008-10-27 : 11:30:58

I need to select a data from a field called parameters >from SQL reporting services 2000< but all I want to get is the information between header_Text= and a &

currently somewhere in the field parameters there is always an entry that is

header_text=company name& but I cant figure out how to convert this.

If needed I am joining 2 tables catalog >where ItemID is the key< to ExecutionLog<where ReportID is the key> and the fields I am displaying is "Name" from Catalog and TimeStart and Parameters from ExecutionLog, and parameters is the field I want to parse down.

Thank you if you can help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 11:44:56

use patindex

SELECT SUBSTRING(yourcol,patindex('header_text%',yourcol)+10,charindex('&',yourcol)-patindex('header_text%',yourcol)+1)
will give you data between header_text and &

regelos
Starting Member

13 Posts

Posted - 2008-10-27 : 12:05:12

thank you that almost worked this is what I got

USE ReportServer

SELECT Catalog.Name, ExecutionLog.TimeStart, SUBSTRING(ExecutionLog.Parameters,patindex('header_text%',ExecutionLog.Parameters)+10,charindex('&',ExecutionLog.Parameters)-patindex('header_text%',ExecutionLog.Parameters)+1)
FROM ReportServer.dbo.Catalog Catalog INNER JOIN ReportServer.dbo.ExecutionLog ExecutionLog ON Catalog.ItemID=ExecutionLog.ReportID

but what I am running into a problem with is that the column it returns has

't=COMPANYNAME' and whatever changes I make still has those showing. as well as there is the & symbol with alot more infor afterwards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 12:54:24

ok. do small modification

USE ReportServer

SELECT Catalog.Name, ExecutionLog.TimeStart, SUBSTRING(ExecutionLog.Parameters,patindex('header_text%',ExecutionLog.Parameters)+12,charindex('&',ExecutionLog.Parameters)-patindex('header_text%',ExecutionLog.Parameters)+1)
FROM ReportServer.dbo.Catalog Catalog INNER JOIN ReportServer.dbo.ExecutionLog ExecutionLog ON Catalog.ItemID=ExecutionLog.ReportID

regelos
Starting Member

13 Posts

Posted - 2008-10-28 : 02:45:35

thanks for the help.

that got almost all of it now all thats left is "Company Name&begin_dt=01/" it can also say other things after the & symbol.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 03:50:05

for such cases what do you want to return? the value 01?

regelos
Starting Member

13 Posts

Posted - 2008-10-28 : 07:11:13

nothing I only want to keep the things between header_text and the & symbol >company name< everything else can disapear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 07:39:49

whats the full data in field for the row where you got this value (Company Name&begin_dt=01/)?

regelos
Starting Member

13 Posts

Posted - 2008-10-28 : 08:45:07

header_text=COMPANY NAME&header_parameters=PARAMETER: PARAMETER / Ship: SHIP SHIP / Departure Date: 7/7/2007&order_field=cabin_cat_code&cruise_line_id=4&ship_id=256&grp_id=0&pri_act_begin_dt=07/07/2007 00:00:00&product_type_code:isnull=true&store_id=996&counselor_login=LOGIN1&include_shared_ind=False&grp_leader_id=0

is one example but the header_text line is not always at the begining of the field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:24:51

where is begin_dt? how did you get value Company Name&begin_dt=01/ for the above row?

regelos
Starting Member

13 Posts

Posted - 2008-10-28 : 11:58:08

there isnt always the same data after the & it can be one of about 40 diff varients in this case it was something else it doesnt even display the rest of the line just about 8-9 chars afterwards..

the only thing I can guarentee as a constant is that it will start "header_text=" and will have a "&" after the company name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:05:48

nope. i was telling i couldnt find begin_dt in string you posted. I was asking you to post string which produced this output
Company Name&begin_dt=01/

regelos
Starting Member

13 Posts

Posted - 2008-10-28 : 13:39:22

this is the string for that but to be clear they all are leaving trailing things behind the &

header_text=COMPANY NAME&begin_dt=01/01/2001 00:00:00&end_dt=01/01/2001 00:00:00¤t_user=USERNAME¤cy_code=USD&SumView_ind=True&DetView_ind=False&vendor_id=7104,10410,2971,100165,2,4091,7626,8526&new_ind=True&adj_ind=True&can_ind=True&dep_ind

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 15:16:38

[code]USE ReportServer

SELECT Catalog.Name, ExecutionLog.TimeStart, SUBSTRING(ExecutionLog.Parameters,patindex('header_text%',ExecutionLog.Parameters)+12,charindex('&',ExecutionLog.Parameters)-patindex('header_text%',ExecutionLog.Parameters)-12)
FROM ReportServer.dbo.Catalog Catalog INNER JOIN ReportServer.dbo.ExecutionLog ExecutionLog ON Catalog.ItemID=ExecutionLog.ReportID
[/code]

quote:
Originally posted by visakh16

ok. do small modification
[code]USE ReportServer

SELECT Catalog.Name, ExecutionLog.TimeStart, SUBSTRING(ExecutionLog.Parameters,patindex('header_text%',ExecutionLog.Parameters)+12,charindex('&',ExecutionLog.Parameters)-patindex('header_text%',ExecutionLog.Parameters)+1)
FROM ReportServer.dbo.Catalog Catalog INNER JOIN ReportServer.dbo.ExecutionLog ExecutionLog ON Catalog.ItemID=ExecutionLog.ReportID
[/code]

regelos
Starting Member

13 Posts

Posted - 2008-10-29 : 09:03:34

thank you thank you thank you thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources