April 15, 2013 at 7:36 am
Hi,
I need an update query which will get the 'scenario' and 'actType' values from all the rows in column strComment and insert the values of 'scenario' into column keyScenario and the value of 'actType' into column act_Act.
Please look at the picture attached.
Thank you in advance.
April 15, 2013 at 7:41 am
your question needs the same solution you can see in this link from a couple of days ago:
if you post DDL and sample data the way you see in that post, you could get better help that exactly solves your problem:
http://www.sqlservercentral.com/Forums/FindPost1440295.aspx
Lowell
April 15, 2013 at 8:04 am
Thank you for your reply.
Here is the DDL and the sample data as requested
USE [DB_Access]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [audit].[tblScenarioRunAudit](
[id] [int] IDENTITY(1,1) NOT NULL,
[keyScenario] [int] NULL,
[strComment] [varchar](8000) NULL,
[act_ActType] [tinyint] NULL,
CONSTRAINT [PK_tblScenarioRunAudit] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Sample Data from column strComment:
{ scenario: 193; actType: 9}
{ scenario: 313; actType: 9}
{ scenario: 233; actType: 7}
{ scenario: 235; actType: 7}
{ scenario: 331; actType: 8}
{ scenario: 269; actType: 7}
{ scenario: 271; actType: 7}
{ scenario: 144; actType: 254}
{ scenario: 227; actType: 6}
{ scenario: 229; actType: 6}
April 15, 2013 at 8:15 am
your sample data really needs to INSERT into your table you posted; all we will really have is whatever you post here; if we cannot simply run it in SSMS, we spend time cleaning up your data to be "consumable" instead of providing a solution.
here's an exmaple , exactly based on what i posted, with minor things for you to clean up:
/*
scenario acttype C1 C2 strComment
193 9} { scenario: 193 actType: 9} { scenario: 193; actType: 9}
313 9} { scenario: 313 actType: 9} { scenario: 313; actType: 9}
233 7} { scenario: 233 actType: 7} { scenario: 233; actType: 7}
235 7} { scenario: 235 actType: 7} { scenario: 235; actType: 7}
331 8} { scenario: 331 actType: 8} { scenario: 331; actType: 8}
269 7} { scenario: 269 actType: 7} { scenario: 269; actType: 7}
271 7} { scenario: 271 actType: 7} { scenario: 271; actType: 7}
144 254} { scenario: 144 actType: 254} { scenario: 144; actType: 254}
227 6} { scenario: 227 actType: 6} { scenario: 227; actType: 6}
229 6} { scenario: 229 actType: 6} { scenario: 229; actType: 6}
*/
With mySampleData(strComment)
AS
(
SELECT '{ scenario: 193; actType: 9}' UNION ALL
SELECT '{ scenario: 313; actType: 9}' UNION ALL
SELECT '{ scenario: 233; actType: 7}' UNION ALL
SELECT '{ scenario: 235; actType: 7}' UNION ALL
SELECT '{ scenario: 331; actType: 8}' UNION ALL
SELECT '{ scenario: 269; actType: 7}' UNION ALL
SELECT '{ scenario: 271; actType: 7}' UNION ALL
SELECT '{ scenario: 144; actType: 254}' UNION ALL
SELECT '{ scenario: 227; actType: 6}' UNION ALL
SELECT '{ scenario: 229; actType: 6}'
)
,IntermediateResults
AS
(
SELECT
dbo.fn_parsename(strComment,';',1) As C1,
dbo.fn_parsename(strComment,';',2) As C2,
mySampleData.*
FROM mySampleData
)
select
dbo.fn_parsename(C1,':',2) As scenario,
dbo.fn_parsename(C2,':',2) As acttype,
IntermediateResults.*
FROM IntermediateResults
Lowell
April 15, 2013 at 8:35 am
Thank you
April 15, 2013 at 8:56 am
Hi,
When i run your code, on the second column actType I get '}' with all the results.
For example:
254}
254}
254}
Is it possible for you to remove the '}' from the results.
Thank you
April 15, 2013 at 9:04 am
maxlezious (4/15/2013)
Hi,When i run your code, on the second column actType I get '}' with all the results.
For example:
254}
254}
254}
Is it possible for you to remove the '}' from the results.
Thank you
The point Lowell was making is that YOU need to clean up your code so that we can work on your problem instead of donating our time to cleanup your post into a usable format.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2013 at 9:24 am
Sorry i am new to the forum and I will keep that in mind for my future posts.
I am executing
With mySampleData(strComment)
AS
(
SELECT TOP 100 strComment FROM [DB_access].[audit].[tblScenarioRunAudit]
WHERE strComment LIKE '%scenario%'
)
,IntermediateResults
AS
(
SELECT
dbo.fn_parsename(strComment,';',1) As C1,
dbo.fn_parsename(strComment,';',2) As C2,
mySampleData.*
FROM mySampleData
)
select
dbo.fn_parsename(C1,':',2) As scenario,
dbo.fn_parsename(C2,':',2) As actType,
IntermediateResults.*
FROM IntermediateResults
In the result set i am getting '}' in the actType column like showed in the attached picture.
Can you please help me remove the '}'
Thank you
April 15, 2013 at 9:42 am
We know you are new around here and we really do want to help.
The problem is that we can't use your data. Here is what you posted.
Sample Data from column strComment:
{ scenario: 193; actType: 9}
{ scenario: 313; actType: 9}
{ scenario: 233; actType: 7}
{ scenario: 235; actType: 7}
{ scenario: 331; actType: 8}
{ scenario: 269; actType: 7}
{ scenario: 271; actType: 7}
{ scenario: 144; actType: 254}
{ scenario: 227; actType: 6}
{ scenario: 229; actType: 6}
Now obviously we can't hit F5 in SSMS and have data in the table. Lowell posted one way to turn that into something useful. I'm afraid that in his attempt to help he has confused you.
Forget what Lowell posted for now (although in the future you might find that kind of thing very useful). Instead turn your sample data into something that is consumable.
Like this:
insert tblScenarioRunAudit (keyScenario, act_ActType)
select 193, 9 union all
select 313, 9
etc...
Doing this means we have your table AND data by simply pushing F5. We are all volunteers around here and most of us answer questions during our freetime. We don't need practice setting up data so we ask people to do the tedious setup so that we can focus our time on the issue instead of setting up the problem.
Hopefully this explains what we haven't made much progress on your issue as of yet. I have now spent all the time I have before my next commitment. I hope you will be able to post your data in a format we can use. If nobody else comes along after you post data I will see if I can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2013 at 10:32 am
Sean's been very helpful here as well; when you ask strangers for help, you've got to put some effort and thought into it yourself. YOU are the one who has to support any changes in the future, or for adapting the same techniques to other similar problems.
you were able to adapt my example easily, based on your last post...just think it through:
if one of the values has an extra '}' in it, you could use the REPLACE function to remove it.
if the results are text/varchar, but you need them to be integers, you could CAST or CONVERT the results.
a disconnected example for you to plug in and use as a model:
--remove one specific undesirable character:
REPLACE(dbo.fn_parsename(C2,':',2),'}','') As actType,
--but i need the results to be an integer and not a string of numbers?
CONVERT(,int,REPLACE(dbo.fn_parsename(C2,':',2),'}','')) As actType,
Lowell
April 15, 2013 at 11:08 am
Sean i totally agree with what you said and apologies i didnt post the data in the correct format. In future ill make sure I post all the required data so I dont waste anyones time.
Lowell I am new sql and still learning all the functions. The replace function did the trick.
I just want to say thank you guys for your time, you guys have been very helpful.
April 15, 2013 at 11:27 am
maxlezious (4/15/2013)
Sean i totally agree with what you said and apologies i didnt post the data in the correct format. In future ill make sure I post all the required data so I dont waste anyones time.Lowell I am new sql and still learning all the functions. The replace function did the trick.
I just want to say thank you guys for your time, you guys have been very helpful.
No problem. I was trying to take a different approach to explaining so you could understand what we were asking. This type of thing is difficult to post when you are unsure what to do. Please come back and ask for help anytime.
I am glad you were able to figure out the issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply