July 21, 2017 at 6:33 am
I have a set of records containing strings similar to [Update CITY_ID to (xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx) and update SITE_DESC to (My New Description Text)]. The description text varies in length and is consistently found in all rows by charindex 'SITE_DESC to ('
Where it exists, the text [My New Description Text] needs to be extracted into a new column [New_SITE_DESC] on a temp table [#update_site] , so that it can be used as part of a larger update script.
Can anyone help me with a short line of code or snippet that will do this?
Thanks in advance for any help you are willing to post.
July 21, 2017 at 6:43 am
This would be a lot easier to help with if you could post some sample data and the necessary CREATE TABLE and INSERT statements. You will want to know if there are any data conditions in your existing data that could cause problems with a given methodology, so to be sure to test, test, and re-test any particular method.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 21, 2017 at 6:48 am
In particular, is it possible to have a right parenthesis ")" within the text being captured? Based only on your post, there's no way to know if there are going to be other elements beyond the one you're trying to retrieve. Is the desired element always the last one? Those are the kinds of questions I was concerned about when I suggested that you "test, test, and re-test" a given methodology. The real question is how to know for certain that one has reached the end of the desired text element.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 21, 2017 at 7:27 am
Thanks for helping me!
The string in my post is typical, and there will never be parenthesis within the description text.
I have generated a fictional sample of records and a create table query for you:
Create table:
CREATE TABLE [dbo].[_1UPDATE_SITE_DESC_list](
[RowNum] [INT] NULL,
[SITE_ID] [UNIQUEIDENTIFIER] NULL,
[ACTION] [VARCHAR](255) NULL
) ON [PRIMARY]
July 21, 2017 at 9:11 am
You were asked to supply the sample data as INSERT statements, not as a spreadsheet. Many people hesitate to open files from the Internet, because of the potential for harmful content in those files.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 21, 2017 at 10:10 am
Sorry, I am new to SSC and this is my first post... Thanks for your patience.
CREATE TABLE _1UPDATE
(
RowNum INT NULL
, SITE_ID UNIQUEIDENTIFIER NULL
, Actions VARCHAR (255) NULL
) ON [PRIMARY];
GO
INSERT INTO
_1UPDATE
(
RowNum
, SITE_ID
, Actions
)
VALUES
(
'1'
, '80cd6bf3-4063-4205-b1b8-4c627536f6a8 '
, 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Varchar with 255 possible field length) '
)
, (
'2'
, 'bdb9e631-01d5-2c0c-fb46-1032f22f254d '
, 'Update CITY_ID to (D3E73DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Text D. Words with mixed case) '
)
, (
'3', 'e829d82a-06ad-2e14-f588-e24f22e01651 ', 'Update SITE_DESC to (comments about desc) '
)
, (
'4', '85791070-a26d-b243-5032-0ea22e7ed767 ', 'Update SITE_DESC to (a short text) '
)
, (
'5'
, 'e0023d24-19e5-3d47-9d1f-8b9239e38632 '
, 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (some desc text with lots of letters and numbers) '
)
, (
'6'
, 'e142bf92-91ce-bb58-03c3-c48026141592 '
, 'Update CITY_ID to (83E93DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Another Description ) '
)
, (
'7'
, '8fd5ef87-c496-099e-c9f6-de234f01a676 '
, 'plorum homo in Sed Versus trepicandor Versus cognitio Tam delerium. quad venit. gravis rarendum transit. '
)
, (
'8'
, 'aa4b1131-d8bc-942e-37ea-c2c490a733cb '
, 'brevens et linguens si quo bono quad estis et et plorum rarendum pars novum trepicandor quis quad Versus '
)
, (
'9'
, '84d4dfac-fc43-a074-152e-4adead58c252 '
, 'volcans trepicandor trepicandor Sed quartu non Et quad glavans non apparens et Quad plorum cognitio '
)
, (
'10'
, 'b826fbbe-2526-f8b1-1356-94b31693858b '
, 'nomen delerium. parte linguens estis si estum. brevens imaginator Et estis gravis si in plorum quad et '
);
GO
July 21, 2017 at 10:22 am
See if this is producing the desired results...
IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL
BEGIN -- DROP TABLE #TestData;
CREATE TABLE #TestData (
RowNum INT NOT NULL PRIMARY KEY CLUSTERED,
SITE_ID UNIQUEIDENTIFIER NOT NULL,
[Action] VARCHAR(1000) NOT NULL
);
INSERT #TestData (RowNum, SITE_ID, [Action])
SELECT
x.RowNum, x.SITE_ID, x.[Action]
FROM
( VALUES
(1, '80cd6bf3-4063-4205-b1b8-4c627536f6a8', 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Varchar with 255 possible field length)'),
(2, 'bdb9e631-01d5-2c0c-fb46-1032f22f254d', 'Update CITY_ID to (D3E73DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Text D. Words with mixed case)'),
(3, 'e829d82a-06ad-2e14-f588-e24f22e01651', 'Update SITE_DESC to (comments about desc)'),
(4, '85791070-a26d-b243-5032-0ea22e7ed767', 'Update SITE_DESC to (a short text)'),
(5, 'e0023d24-19e5-3d47-9d1f-8b9239e38632', 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (some desc text with lots of letters and numbers)'),
(6, 'e142bf92-91ce-bb58-03c3-c48026141592', 'Update CITY_ID to (83E93DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Another Description )'),
(7, '8fd5ef87-c496-099e-c9f6-de234f01a676', 'plorum homo, in Sed Versus trepicandor Versus cognitio, Tam delerium. quad venit. gravis rarendum transit.'),
(8, 'aa4b1131-d8bc-942e-37ea-c2c490a733cb', 'brevens, et linguens si quo bono quad estis et et plorum rarendum pars novum trepicandor quis quad Versus'),
(9, '84d4dfac-fc43-a074-152e-4adead58c252', 'volcans trepicandor trepicandor Sed quartu non Et quad glavans non apparens et Quad plorum cognitio,'),
(10, 'b826fbbe-2526-f8b1-1356-94b31693858b', 'nomen delerium. parte linguens estis si estum. brevens, imaginator Et estis gravis si in plorum quad et')
) x (RowNum, SITE_ID, [Action]) ;
END;
--============================================================================================================
SELECT
td.RowNum, td.SITE_ID,
ParsedValue = SUBSTRING(td.[Action], spb.SecondParenBeg + 1, spe.SecondParenEnd - spb.SecondParenBeg -1)
FROM
#TestData td
CROSS APPLY ( VALUES (CHARINDEX(')', td.[Action], 1)) ) fpe (FirstParenEnd)
CROSS APPLY ( VALUES (NULLIF(CHARINDEX('(', td.[Action], fpe.FirstParenEnd + 1), 0)) ) spb (SecondParenBeg)
CROSS APPLY ( VALUES (NULLIF(CHARINDEX(')', td.[Action], spb.SecondParenBeg + 1), 0)) ) spe (SecondParenEnd);
HTH,
Jason
July 21, 2017 at 10:29 am
I chose to take the chance and help out, so here you go:CREATE TABLE dbo._1UPDATE_SITE_DESC_list (
RowNum int NULL,
SITE_ID uniqueidentifier NULL,
[ACTION] varchar(255) NULL
);
INSERT INTO dbo._1UPDATE_SITE_DESC_list (RowNum, SITE_ID, [ACTION])
VALUES (1, '80cd6bf3-4063-4205-b1b8-4c627536f6a8', 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Varchar with 255 possible field length)'),
(2, 'bdb9e631-01d5-2c0c-fb46-1032f22f254d', 'Update CITY_ID to (D3E73DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Text D. Words with mixed case)'),
(3, 'e829d82a-06ad-2e14-f588-e24f22e01651', 'Update SITE_DESC to (comments about desc)'),
(4, '85791070-a26d-b243-5032-0ea22e7ed767', 'Update SITE_DESC to (a short text)'),
(5, 'e0023d24-19e5-3d47-9d1f-8b9239e38632', 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (some desc text with lots of letters and numbers)'),
(6, 'e142bf92-91ce-bb58-03c3-c48026141592', 'Update CITY_ID to (83E93DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Another Description )'),
(7, '8fd5ef87-c496-099e-c9f6-de234f01a676', 'plorum homo, in Sed Versus trepicandor Versus cognitio, Tam delerium. quad venit. gravis rarendum transit.'),
(8, 'aa4b1131-d8bc-942e-37ea-c2c490a733cb', 'brevens, et linguens si quo bono quad estis et et plorum rarendum pars novum trepicandor quis quad Versus'),
(9, '84d4dfac-fc43-a074-152e-4adead58c252', 'volcans trepicandor trepicandor Sed quartu non Et quad glavans non apparens et Quad plorum cognitio,'),
(10, 'b826fbbe-2526-f8b1-1356-94b31693858b', 'nomen delerium. parte linguens estis si estum. brevens, imaginator Et estis gravis si in plorum quad et');
SELECT SD.RowNum,
SUBSTRING(SD.[ACTION], CHARINDEX('SITE_DESC to (', SD.[ACTION]) + 14, CHARINDEX(')', SD.[ACTION], CHARINDEX('SITE_DESC to (', SD.[ACTION]) + 14) - CHARINDEX('SITE_DESC to (', SD.[ACTION]) - 14) AS Site_Description
FROM dbo._1UPDATE_SITE_DESC_list AS SD
WHERE CHARINDEX('SITE_DESC to (', SD.[ACTION]) > 0
ORDER BY SD.RowNum;
DROP TABLE dbo._1UPDATE_SITE_DESC_list;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 21, 2017 at 10:29 am
Awesome! That is exactly the result I was looking for.
I really appreciate the help. Thank you very, very much!!
-Terry
July 21, 2017 at 10:43 am
terrence.j.buda - Friday, July 21, 2017 10:29 AMAwesome! That is exactly the result I was looking for.I really appreciate the help. Thank you very, very much!!
-Terry
Who are you replying to?
July 21, 2017 at 10:47 am
sgmunson - Friday, July 21, 2017 10:29 AMI chose to take the chance and help out, so here you go:CREATE TABLE dbo._1UPDATE_SITE_DESC_list (
RowNum int NULL,
SITE_ID uniqueidentifier NULL,
[ACTION] varchar(255) NULL
);
INSERT INTO dbo._1UPDATE_SITE_DESC_list (RowNum, SITE_ID, [ACTION])
VALUES (1, '80cd6bf3-4063-4205-b1b8-4c627536f6a8', 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Varchar with 255 possible field length)'),
(2, 'bdb9e631-01d5-2c0c-fb46-1032f22f254d', 'Update CITY_ID to (D3E73DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Text D. Words with mixed case)'),
(3, 'e829d82a-06ad-2e14-f588-e24f22e01651', 'Update SITE_DESC to (comments about desc)'),
(4, '85791070-a26d-b243-5032-0ea22e7ed767', 'Update SITE_DESC to (a short text)'),
(5, 'e0023d24-19e5-3d47-9d1f-8b9239e38632', 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (some desc text with lots of letters and numbers)'),
(6, 'e142bf92-91ce-bb58-03c3-c48026141592', 'Update CITY_ID to (83E93DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Another Description )'),
(7, '8fd5ef87-c496-099e-c9f6-de234f01a676', 'plorum homo, in Sed Versus trepicandor Versus cognitio, Tam delerium. quad venit. gravis rarendum transit.'),
(8, 'aa4b1131-d8bc-942e-37ea-c2c490a733cb', 'brevens, et linguens si quo bono quad estis et et plorum rarendum pars novum trepicandor quis quad Versus'),
(9, '84d4dfac-fc43-a074-152e-4adead58c252', 'volcans trepicandor trepicandor Sed quartu non Et quad glavans non apparens et Quad plorum cognitio,'),
(10, 'b826fbbe-2526-f8b1-1356-94b31693858b', 'nomen delerium. parte linguens estis si estum. brevens, imaginator Et estis gravis si in plorum quad et');SELECT SD.RowNum,
SUBSTRING(SD.[ACTION], CHARINDEX('SITE_DESC to (', SD.[ACTION]) + 14, CHARINDEX(')', SD.[ACTION], CHARINDEX('SITE_DESC to (', SD.[ACTION]) + 14) - CHARINDEX('SITE_DESC to (', SD.[ACTION]) - 14) AS Site_Description
FROM dbo._1UPDATE_SITE_DESC_list AS SD
WHERE CHARINDEX('SITE_DESC to (', SD.[ACTION]) > 0
ORDER BY SD.RowNum;DROP TABLE dbo._1UPDATE_SITE_DESC_list;
This produces what I need. All of the rows which have actions to update 'SITE_DESC to (' have the action spelled out correctly.
Thanks for taking the time to help me! I have been spinning my wheels trying to get this right for days. Thanks for taking the time to help me! I have been spinning my wheels trying to get this right for days.
:w00t:
July 21, 2017 at 10:52 am
Jason A. Long - Friday, July 21, 2017 10:43 AMterrence.j.buda - Friday, July 21, 2017 10:29 AMAwesome! That is exactly the result I was looking for.I really appreciate the help. Thank you very, very much!!
-TerryWho are you replying to?
Sorry Jason, I got confused with the responses... Steve got the exact result I needed. Your result did not get the example data where 'SITE_ID to (' appears as the only set of parenthesis on a row. Your script will work for another data set that I am trying to parse. I really appreciate you taking the time to respond! So glad I decided to join this forum.
Best regards,
-Terry
July 21, 2017 at 11:06 am
terrence.j.buda - Friday, July 21, 2017 10:52 AMJason A. Long - Friday, July 21, 2017 10:43 AMterrence.j.buda - Friday, July 21, 2017 10:29 AMAwesome! That is exactly the result I was looking for.I really appreciate the help. Thank you very, very much!!
-TerryWho are you replying to?
Sorry Jason, I got confused with the responses... Steve got the exact result I needed. Your result did not get the example data where 'SITE_ID to (' appears as the only set of parenthesis on a row. Your script will work for another data set that I am trying to parse. I really appreciate you taking the time to respond! So glad I decided to join this forum.
Best regards,
-Terry
No worries. I read right over that part and got focused on "between 2nd set of parens"... You'll do well w/ Steve's solution.
July 24, 2017 at 7:00 am
terrence.j.buda - Friday, July 21, 2017 10:47 AMsgmunson - Friday, July 21, 2017 10:29 AMI chose to take the chance and help out, so here you go:CREATE TABLE dbo._1UPDATE_SITE_DESC_list (
RowNum int NULL,
SITE_ID uniqueidentifier NULL,
[ACTION] varchar(255) NULL
);
INSERT INTO dbo._1UPDATE_SITE_DESC_list (RowNum, SITE_ID, [ACTION])
VALUES (1, '80cd6bf3-4063-4205-b1b8-4c627536f6a8', 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Varchar with 255 possible field length)'),
(2, 'bdb9e631-01d5-2c0c-fb46-1032f22f254d', 'Update CITY_ID to (D3E73DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Text D. Words with mixed case)'),
(3, 'e829d82a-06ad-2e14-f588-e24f22e01651', 'Update SITE_DESC to (comments about desc)'),
(4, '85791070-a26d-b243-5032-0ea22e7ed767', 'Update SITE_DESC to (a short text)'),
(5, 'e0023d24-19e5-3d47-9d1f-8b9239e38632', 'Update CITY_ID to (9FFE20A3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (some desc text with lots of letters and numbers)'),
(6, 'e142bf92-91ce-bb58-03c3-c48026141592', 'Update CITY_ID to (83E93DA3-DF63-E111-A267-60D819FCB8F8) and update SITE_DESC to (Another Description )'),
(7, '8fd5ef87-c496-099e-c9f6-de234f01a676', 'plorum homo, in Sed Versus trepicandor Versus cognitio, Tam delerium. quad venit. gravis rarendum transit.'),
(8, 'aa4b1131-d8bc-942e-37ea-c2c490a733cb', 'brevens, et linguens si quo bono quad estis et et plorum rarendum pars novum trepicandor quis quad Versus'),
(9, '84d4dfac-fc43-a074-152e-4adead58c252', 'volcans trepicandor trepicandor Sed quartu non Et quad glavans non apparens et Quad plorum cognitio,'),
(10, 'b826fbbe-2526-f8b1-1356-94b31693858b', 'nomen delerium. parte linguens estis si estum. brevens, imaginator Et estis gravis si in plorum quad et');SELECT SD.RowNum,
SUBSTRING(SD.[ACTION], CHARINDEX('SITE_DESC to (', SD.[ACTION]) + 14, CHARINDEX(')', SD.[ACTION], CHARINDEX('SITE_DESC to (', SD.[ACTION]) + 14) - CHARINDEX('SITE_DESC to (', SD.[ACTION]) - 14) AS Site_Description
FROM dbo._1UPDATE_SITE_DESC_list AS SD
WHERE CHARINDEX('SITE_DESC to (', SD.[ACTION]) > 0
ORDER BY SD.RowNum;DROP TABLE dbo._1UPDATE_SITE_DESC_list;
This produces what I need. All of the rows which have actions to update 'SITE_DESC to (' have the action spelled out correctly.
Thanks for taking the time to help me! I have been spinning my wheels trying to get this right for days. :w00t:
Glad I could help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply