April 5, 2010 at 3:54 am
I have a string '======Heading========== some phrase ===============bottom line=============='
I need to exrtact string 'some phrase'
pattern is like when i get the word heading in my string
then i will search end of '=' then i start collecting characters untill i get the same '=' again
Can anyone give me the idea how to do that...??
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 5, 2010 at 4:49 am
DECLARE @String VARCHAR(8000),
@Header VARCHAR(8000),
@Footer VARCHAR(8000);
SET @String = '======Heading========== some phrase ===============bottom line==============';
SELECT @Header = '======Heading==========',
@Footer = '===============bottom line==============';
SELECT RTRIM(LTRIM(REPLACE(REPLACE(@String, @Header, SPACE(0)), @Footer, SPACE(0))));
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 5:24 am
DECLARE @String VARCHAR(8000)
SET @String = '======Heading========== some phrase
===============bottom line==============';
SELECT LTRIM(RTRIM(Replace(Replace(replace
(@string, '=', ''), 'Heading', ''), 'bottom line', '')))
Ohh sorry, its the same one as Paul is saying, dint notice!
(perhaps, if the number of '=' varies then this might help. :-))
---------------------------------------------------------------------------------
April 5, 2010 at 6:11 am
Actually my full string is
'CREATE PROCEDURE [dbo].[test] @INID BIGINT /*-------------------------------------------------------------------- DECLARE @b-2 INT EXEC test 1 ====================PURPOSE=========================================== Delete ID =====================INPUT============================================ --------------------------------------------------------------------*/ some code here set nocount on select xyz from abc '
this is script of stored procedures which i am extracting from sys.syscomments so i need to extract the purpose from script for documentation but above code is not working for this string.
Please assist me i have a lot of procedures in my database i have to automate that.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 5, 2010 at 6:14 am
Would have helped if the extra information had been in the first post.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 6:32 am
Updated implementation:
DECLARE @String NVARCHAR(MAX);
SET @String = N'======Heading========== some phrase ===============bottom line==============';
SELECT extract =
SUBSTRING(data, Start.pos, Length.value)
FROM (
SELECT data = @String
) S
CROSS
APPLY (
-- Find the location of the phrase '=Heading='
SELECT CHARINDEX(N'=Heading=', data) + LEN(N'=Heading=')
) Header (pos)
CROSS
APPLY (
-- Find the first character that is not '=' after the header
SELECT Header.pos + PATINDEX(N'%[^=]%', SUBSTRING(data, Header.pos, DATALENGTH(data)))
) Start (pos)
CROSS
APPLY (
-- Find the first occurrence of '==' after Start.pos
SELECT CHARINDEX(N'===', data, Start.pos) - Start.pos - 1
) Length (value)
Notice that sys.syscomments is a compatibility view and deprecated. It will be removed from SQL Server in a future release. Use sys.sql_modules instead.
I use NVARCHAR(MAX) in my example code, since that is the type of the definition column in sys.sql_modules.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2010 at 6:43 am
Paul White NZ (4/5/2010)
Updated implementation:
DECLARE @String NVARCHAR(MAX);
SET @String = N'======Heading========== some phrase ===============bottom line==============';
SELECT extract =
SUBSTRING(data, Start.pos, Length.value)
FROM (
SELECT data = @String
) S
CROSS
APPLY (
-- Find the location of the phrase '=Heading='
SELECT CHARINDEX(N'=Heading=', data) + LEN(N'=Heading=')
) Header (pos)
CROSS
APPLY (
-- Find the first character that is not '=' after the header
SELECT Header.pos + PATINDEX(N'%[^=]%', SUBSTRING(data, Header.pos, DATALENGTH(data)))
) Start (pos)
CROSS
APPLY (
-- Find the first occurrence of '==' after Start.pos
SELECT CHARINDEX(N'===', data, Start.pos) - Start.pos - 1
) Length (value)
Notice that sys.syscomments is a compatibility view and deprecated. It will be removed from SQL Server in a future release. Use sys.sql_modules instead.
I use NVARCHAR(MAX) in my example code, since that is the type of the definition column in sys.sql_modules.
I am sorry but i thought to give simpler sample data but it became confusing
thanx for your quick reply but how can i replace @string with my column name like
select text from sys.syscomments
or
select definition from sys.sql_modules
as i have to use it with the table otherwise i will have to use it by storing the value of column one by one in variable.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 5, 2010 at 7:10 am
vaibhav.tiwari (4/5/2010)
...but how can i replace @string with my column name...as i have to use it with the table otherwise i will have to use it by storing the value of column one by one in variable.
It's quite easy, so I will leave you to examine how the code works and try to answer that question for yourself.
If you get totally stuck after making your best effort, post your attempt, and I (or someone else) will point you in the right direction.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 12:31 am
Paul White NZ (4/5/2010)
vaibhav.tiwari (4/5/2010)
...but how can i replace @string with my column name...as i have to use it with the table otherwise i will have to use it by storing the value of column one by one in variable.It's quite easy, so I will leave you to examine how the code works and try to answer that question for yourself.
If you get totally stuck after making your best effort, post your attempt, and I (or someone else) will point you in the right direction.
Okey dear, I will do it...
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 6, 2010 at 1:14 am
Thank you all very much for your replies and efforts.
Paul i m sorry but your solution is working fine with the string what i have given to you but in my original store procedures its not working fine
I have a lot of procedures and starting pattern of procedures are like as below
CREATE PROCEDURE [dbo].[Archive_PatchWSUSDetail]
/*------------------------------------------------------------------------
[Archive_PatchWSUSDetail]
=====================PURPOSE===============================================
SYNOPSIS: THIS STORE PROCEDURE USING FOR TRANSFERRING DATA FROM Pth_PatchWSUSDetail TO BACKUP DATABASE ACHIVE TABLE
=====================INPUT PARAMETER ======================================
=====================CREATED BY/DATE ======================================
VAIBHAV
02 NOVEMBER 2009
---------------------------------------------------------------------------*/
AS
So this will be saved in the sys.sql_modules in definition column so i have to extract the purpose of procedure for the documentation
so i have done one solution as previous solution is being so much complicated for me
see my solution
DROP TABLE #SpNames
GO
CREATE TABLE #SpNames ( id int, Name VARCHAR(MAX) )
GO
INSERT INTO #SpNames
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1) ) id, Name FROM SYS.PROCEDURES
--SELECT * FROM #SpNames
DECLARE @FinalOutput AS TABLE ( SpName VARCHAR(200), Purpose VARCHAR(MAX) )
DECLARE @Script AS TABLE ( RNo INT identity(1,1) , CodeLine VARCHAR(MAX) )
WHILE EXISTS( SELECT 1 FROM #SpNames)
BEGIN
DECLARE @SpName AS VARCHAR(200)
SET @SpName = (SELECT TOP 1 Name FROM #SpNames ORDER BY ID)
INSERT INTO @Script
EXEC SP_HELPTEXT @SpName
INSERT INTO @FinalOutput
SELECT @SpName, CASE WHEN CodeLine LIKE '=%' THEN NULL ELSE CodeLine END FROM @Script WHERE Rno = (SELECT Rno FROM @Script WHERE CodeLine Like '%=purpose=%') + 1
DELETE TOP(1) FROM #SpNames
DELETE FROM @Script
END
SELECT * FROM @FinalOutput
it reached very near to my requirement but the problem that
there might be possible that there is gap after =========purpose======= line
and i have to get the line which is not blank and between
=============purpose========== and =============input==========
if nothing is there then other than blank it should return me null
Please see the pattern of starting of stored procedure above in red font
Please help me how to do that
please ask me if i was not able to make you understand the things....
Thanks in advance
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 6, 2010 at 5:31 am
USE tempdb;
GO
IF OBJECT_ID(N'dbo.Archive_PatchWSUSDetail', N'P')
IS NOT NULL
DROP PROCEDURE dbo.Archive_PatchWSUSDetail;
IF OBJECT_ID(N'dbo.ExtractString', N'IF')
IS NOT NULL
DROP FUNCTION dbo.ExtractString
GO
-- Test procedure definition
CREATE PROCEDURE [dbo].[Archive_PatchWSUSDetail]
/*------------------------------------------------------------------------
[Archive_PatchWSUSDetail]
=====================PURPOSE===============================================
SYNOPSIS: THIS STORE PROCEDURE USING FOR TRANSFERRING DATA FROM Pth_PatchWSUSDetail TO BACKUP DATABASE ACHIVE TABLE
=====================INPUT PARAMETER ======================================
=====================CREATED BY/DATE ======================================
VAIBHAV
02 NOVEMBER 2009
---------------------------------------------------------------------------*/
AS
BEGIN
RETURN 0;
END;
GO
-- Function to extract a string
CREATE FUNCTION dbo.ExtractString
(
@SearchString NVARCHAR(MAX),
@StartPointString NVARCHAR(10),
@ScanPastChar NCHAR(1),
@EndPointString NVARCHAR(10)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT extract =
SUBSTRING(data, Start.pos, Length.value)
FROM (
SELECT data = @SearchString
) S
CROSS
APPLY (
-- Find the location of the phrase '=Heading='
SELECT CHARINDEX(@StartPointString, data) + LEN(@StartPointString)
) Header (pos)
CROSS
APPLY (
-- Find the first character that is not '=' after the header
SELECT Header.pos + PATINDEX(N'%[^' + @ScanPastChar + N']%', SUBSTRING(data, Header.pos, DATALENGTH(data)))
) Start (pos)
CROSS
APPLY (
-- Find the first occurrence of '==' after Start.pos
SELECT CHARINDEX(@EndPointString, data, Start.pos) - Start.pos - 1
) Length (value)
-- Test
SELECT P.name,
ES.extract
FROM sys.procedures P
JOIN sys.sql_modules M
ON M.object_id = P.object_id
AND P.is_ms_shipped = 0
AND P.type_desc = N'SQL_STORED_PROCEDURE'
CROSS
APPLY dbo.ExtractString (M.definition, N'=PURPOSE=', N'=', N'===') ES;
GO
-- Tidy up
DROP PROCEDURE dbo.Archive_PatchWSUSDetail;
DROP FUNCTION dbo.ExtractString;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 5:44 am
Thanks Paul,
Its working Good one
but for some procedure its showing invalid character length and i observed that there is not purpose line in that i need to return null value for that
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 6, 2010 at 5:48 am
vaibhav.tiwari (4/6/2010)
...but for some procedure its showing invalid character length and i observed that there is not purpose line in that i need to return null value for that
Yes, the function is not bullet-proof or production-quality necessarily.
I'm sure you will be able to modify the code I posted to cope with missing 'purpose' sections.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 6:21 am
Paul White NZ (4/6/2010)
vaibhav.tiwari (4/6/2010)
...but for some procedure its showing invalid character length and i observed that there is not purpose line in that i need to return null value for thatYes, the function is not bullet-proof or production-quality necessarily.
I'm sure you will be able to modify the code I posted to cope with missing 'purpose' sections.
Thanks Paul,
I have changed your code according that now its working fine
If purpose line was not found it was passing -ve value to the funtion i made it null
so now extract value will be null
now if stored procedure is not in proper format it will return null
thanks for you efforts....
Now its working fine and it helps me a lot....
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
April 6, 2010 at 6:26 am
Good to hear, thanks.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply