March 8, 2017 at 7:26 am
Hey all,
I have a query and I cant quite get my mind round what is required.
So I have a table - called Audit.
It has two fields in it - one called Attributemask, the other is called changedata. It also has a primary key. So it would look something like this
PK_value AttributeMask ChangeData
1 ,10000,20,123,43 ~3~Hello~91~
2 ,100,12,124,12 ~33~1433~~Dan
3 ,132,61,2112,3231 ~~Active~112~1213
4 ,21321,2135,321,61 ~das~sad~33212~Inactive
So basically the mask is comma separated list of attributes and the change data is a ~ separated list of what the attribute changed to. If there are two ~~ in arrow it signifies a blank. Or if there is a ~ a the end its also a blank.
So I need to try and extract this data. I have two queries to try and get out.
1) I just need to know the data for the 61 attribute masks. So for 61 - what is the change data.
2) Would love to get a query to extrapolate all the data in so 4 changes becomes 4 rows.
Finally - one caviat. I don't have write access to the database. Read only so temp objects are fine, but I cant use functions.
Any ideas? I am getting stuck. I thought I was getting somewhere with this trying to find the location of the 61, but then got stuck.
len(SUBSTRING(attributemask,0,CHARINDEX(',61,',attributemask))) -
len(replace(SUBSTRING(attributemask,0,CHARINDEX(',61,',attributemask)),',',''))
Any help much appreciated,
Dan
March 8, 2017 at 7:39 am
Dan
Sounds like you've anticipated that someone is going to suggest using a splitter function. If you can't create one, not even in tempdb, then you'll need to modify the code to to do the splitting on the fly. It's a bit difficult to advise further than that without knowing what your expected results look like.
John
March 8, 2017 at 7:50 am
Have whoever owns the server create a new database for you to do the work. If you are allowed to "blow up" tempdb with a mistake you should be able to do the same to an isolated database that is the only thing on the server you have access to other than the production database you need to process these records from/to.
With your own database you can do this the obvious and WAY more efficient way.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 8, 2017 at 10:53 am
Are you allowed to create a split function in the tempdb db? Ask them. It will make this much easier and more consistent to do. Of course you'll need code to recreate the function later after SQL restart.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2017 at 12:04 pm
ScottPletcher - Wednesday, March 8, 2017 10:53 AMAre you allowed to create a split function in the tempdb db? Ask them. It will make this much easier and more consistent to do. Of course you'll need code to recreate the function later after SQL restart.
I think this would require some kind of check to see that the function was there, or at least trap the error that would arise when you called a non-existent object.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 8, 2017 at 5:45 pm
Try this:
DECLARE @tbl table(PK_value int IDENTITY PRIMARY KEY, AttributeMask varchar(500), ChangeData varchar(500));
INSERT @tbl(AttributeMask, ChangeData)
VALUES
('10000,20,123,43', '~3~Hello~91~')
, ('100,12,124,12', '~33~1433~~Dan')
, ('132,61,2112,3231', '~~Active~112~1213')
, ('21321,2135,321,61', '~das~sad~33212~Inactive');
UPDATE @tbl SET ChangeData=Right(ChangeData,Len(ChangeData)-1);
WITH
AttributeMask (ID, AttributeMaskElement, AttributeMaskElementID)
AS
(
SELECT
t.PK_value
, Substring(t.AttributeMask, sv.number, CharIndex(',', t.AttributeMask+',',sv.number+1)-sv.number)
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM @tbl t
JOIN master.dbo.spt_values sv
ON sv.type='P'
AND sv.number BETWEEN 1 AND Len(AttributeMask)
AND Substring(','+t.AttributeMask,sv.number,1)=','
) ,
ChangeData (ID, ChangeDataElement, ChangeDataElementID)
AS
(
SELECT
t2.PK_value
, Substring(t2.ChangeData, sv2.number, CharIndex('~', t2.ChangeData+'~',sv2.number+1)-sv2.number)
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM @tbl t2
JOIN master.dbo.spt_values sv2
ON sv2.type='P'
AND sv2.number BETWEEN 1 AND Len(t2.ChangeData)
AND Substring('~'+t2.ChangeData,sv2.number,1)='~'
)
SELECT
AttributeMask.ID
, AttributeMask.AttributeMaskElement
, Coalesce(ChangeData.ChangeDataElement,'') ChangeDataElement
FROM AttributeMask
LEFT JOIN ChangeData ON ChangeData.ID = AttributeMask.ID
AND AttributeMask.AttributeMaskElementID=ChangeData.ChangeDataElementID;
March 10, 2017 at 8:39 am
Hi Joe,
Thanks for the message. I am trying it now. Not sure if this is going to be too much (in terms of data - and performance).
Hi all,
Looks like I am going to get some more access on Monday (well a copy of the database anyway!).
So if I did manage to get some access - and used a split function. I am just trying to think of the most efficient way to get the data from being in the split table, to be in rows? Unpivot?
Any advice still gratefully received.
Dan
March 10, 2017 at 8:47 am
Dan
The splitter function will do that for you. It takes a delimited list and returns the elements one per row.
John
March 10, 2017 at 8:54 am
John Mitchell-245523 - Friday, March 10, 2017 8:47 AMDanThe splitter function will do that for you. It takes a delimited list and returns the elements one per row.
John
Ahh your right I think I am remembering wrong.
But if I am splitting two fields how do I ensure they kept in sync (i.e. the first element each field stays together, the second element of each field stays together and so on). Not sure I have tried a split on two fields.
March 10, 2017 at 9:06 am
Like I said before, difficult to say without knowing what your expected results look like. But, from memory, the splitter function returns the element and the ordinal position, so all you should need to do is join on the respective ordinal positions to keep the elements from the two lists married up.
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply