Extract string from same column

Question

Extract string from same column

monilps

SSCommitted

Points: 1686
More actions
April 27, 2016 at 10:32 am

#313541

Hi,
SELECT 'EXEC sp_rename @objname = N''PK_Filegroup_Tables_ID'', @newname = N''PK_FilegroupTables_ID'';' as Script UNION ALL
SELECT 'Admin', 'PrimaryKey', 'FilegroupIndexes', 'EXEC sp_rename @objname = N''PK_Filegroup_Indexes_ID'', @newname = N''PK_FilegroupIndexes_ID'';' as Script
I need below result (Please see attached JPG file), please let me know how achieve it.
ColumnName Script objname newname
Row EXEC sp_rename @objname = N'PK_Filegroup_Tables_ID', @newname = N'PK_FilegroupTables_ID'; PK_Filegroup_Tables_ID PK_FilegroupTables_ID
Row EXEC sp_rename @objname = N'PK_Filegroup_Indexes_ID', @newname = N'PK_FilegroupIndexes_ID'; PK_Filegroup_Indexes_ID PK_FilegroupIndexes_ID
I need to substring the data.
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

monilps (4/27/2016)
Hi,
SELECT 'EXEC sp_rename @objname = N''PK_Filegroup_Tables_ID'', @newname = N''PK_FilegroupTables_ID'';' as Script
SELECT 'Admin', 'PrimaryKey', 'FilegroupIndexes', 'EXEC sp_rename @objname = N''PK_Filegroup_Indexes_ID'', @newname = N''PK_FilegroupIndexes_ID'';' as Script
I would like to extract the data from @objname and @newname from above statement.
I need generalized solution which should work with same format.
For example Result should be like this:
Column Name Script objname newname
Row EXEC sp_rename @objname = N'PK_Filegroup_Tables_ID', @newname = N'PK_FilegroupTables_ID'; PK_Filegroup_Tables_ID PK_FilegroupTables_ID
Row EXEC sp_rename @objname = N'PK_Filegroup_Indexes_ID', @newname = N'PK_FilegroupIndexes_ID'; PK_Filegroup_Indexes_ID PK_FilegroupIndexes_ID
Thanks in advance.

This question doesn't make any sense to me. Are you asking how you can use substring to extract that information? Help us to help you by providing actual details about what you are trying to do. As posted I doubt anybody can help unless they have a crystal ball.

_______________________________________________________________

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/

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

That's hard to read, and as Sean mentioned, it doesn't quite make sense. Please format your results as plain code (shortcut to the left of the edit box), so that spacing works properly. Format SQL code as code-SQL.

I'm guessing somehow you want a script to build a script.

monilps SSCommitted Points: 1686 More actions · Answer 3

I have formatted the question, I did my best to achieve it.

Thank you for your help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442467

April 27, 2016 at 12:30 pm

#1875032

Not sure about anyone else but I am still lost.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Well you formatted the question but obviously didn't bother to actually see if your sql will execute. You don't have the same number of columns in each query but they are unioned.

Also, why do you have these rename statements in a string like this? And really what is the point? You are renaming to the same name they already have.

Just for the sake of argument let's pretend this is your query.

SELECT 'EXEC sp_rename @objname = N''PK_Filegroup_Tables_ID'', @newname = N''PK_FilegroupTables_ID'';' as Script UNION ALL

SELECT 'EXEC sp_rename @objname = N''PK_Filegroup_Indexes_ID'', @newname = N''PK_FilegroupIndexes_ID'';'

There is nothing magical about the contents here. It is just text characters. This is nothing more than basic substring logic. Give it a shot using SUBSTRING and CHARINDEX.

_______________________________________________________________

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/

monilps SSCommitted Points: 1686 More actions · Answer 6

I checked the query and it works fine, can you please make sure you have selected everything. Also objname and newname are different. I am trying to rename PrimaryKey but before that I would like to have a two additional columns one with objname and newname.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

monilps (4/27/2016)
I checked the query and it works fine, can you please make sure you have selected everything. Also objname and newname are different. I am trying to rename PrimaryKey but before that I would like to have a two additional columns one with objname and newname.
Thanks.

This is the error message I get when I select your complete query in your first post:

Msg 205, Level 16, State 1, Line 1

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 8

As Lynn noted, your query is incorrect. There are two different sets of columns in each SELECT. One column in the first, multiple ones in the second.

The format for substring is simple. Substring( string, start, etc). You can do this with any string.

select substring('This string is partially returned', 1, 4)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

If they're all renaming queries, this could work.

WITH SampleData AS(

SELECT 'EXEC sp_rename @objname = N''PK_Filegroup_Tables_ID'', @newname = N''PK_FilegroupTables_ID'';' as Script UNION ALL

SELECT 'EXEC sp_rename @objname = N''PK_Filegroup_Indexes_ID'', @newname = N''PK_FilegroupIndexes_ID'';'

)

SELECT 'Row' AS ColumnName,

sd.Script,

objname = MAX( CASE WHEN sp.Item LIKE 'objname%' THEN /*sp.Item */ SUBSTRING( sp.Item, CHARINDEX('''', sp.Item) + 1, CHARINDEX('''', sp.Item, CHARINDEX('''', sp.Item) + 1) - CHARINDEX('''', sp.Item) - 1) END),

newname = MAX( CASE WHEN sp.Item LIKE 'newname%' THEN /*sp.Item */ SUBSTRING( sp.Item, CHARINDEX('''', sp.Item) + 1, CHARINDEX('''', sp.Item, CHARINDEX('''', sp.Item) + 1) - CHARINDEX('''', sp.Item) - 1) END)

FROM SampleData sd

CROSS APPLY dbo.DelimitedSplit8K(sd.Script, '@') AS sp

GROUP BY sd.Script;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

monilps SSCommitted Points: 1686 More actions · Answer 10

monilps

SSCommitted

Points: 1686

April 28, 2016 at 2:22 pm

#1875400

Thank you all 🙂