August 24, 2016 at 6:22 am
Hi
i need to find whether a set of words for ex. SET NOCOUNT ON in a text. in text it may be SET NOCOUNT ON (with lot of speces in between those words) .
any suggestion on how to achieve this?
declare @txt varchar (500) = 'create proc proc_name as begin set nocount on select 1 end'
declare @src_text varchar(50)='set nocount on'
SELECT 'EXISTS' WHERE @txt like '%'+@src_text+'%
August 24, 2016 at 6:55 am
SqlStarter (8/24/2016)
Hii need to find whether a set of words for ex. SET NOCOUNT ON in a text. in text it may be SET NOCOUNT ON (with lot of speces in between those words) .
any suggestion on how to achieve this?
declare @txt varchar (500) = 'create proc proc_name as begin set nocount on select 1 end'declare @src_text varchar(50)='set nocount on'
SELECT 'EXISTS' WHERE @txt like '%'+@src_text+'%
Why not take out all the spaces from both, and test for the string SETNOCOUNTON ?
Using your variables
SELECT 'EXISTS'
WHERE REPLACE(@txt,space(1),space(0)) LIKE
'%'+REPLACE(@src_text,space(1),space(0))+'%'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2016 at 7:56 am
Here's another option. Note that these solutions won't eliminate tabs, line feeds or other spacing characters. I created a function to avoid making the query too complex and to reuse it on future occasions.
CREATE TABLE #Sample(
Sometext varchar(500)
);
INSERT INTO #Sample
VALUES
('CREATE PROCEDURE Proc1 AS SELECT * FROM sys.tables'),
('CREATE PROCEDURE Proc2 AS SET NOCOUNT ON SELECT * FROM sys.tables'),
('CREATE PROCEDURE Proc3 AS SET NOCOUNT ON SELECT * FROM sys.tables');
GO
CREATE FUNCTION iRemoveSpaces(
@Textvarchar(8000)
)
RETURNS TABLE SCHEMABINDING
AS
RETURN
SELECT replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@Text)),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' ') AS NewText;
GO
SELECT *
FROM #Sample
CROSS APPLY iRemoveSpaces(SomeText) rs
WHERE rs.NewText LIKE '%SET NOCOUNT ON%';
August 24, 2016 at 7:27 pm
There was an article from Jeff Moden how to replace any number of consecutive spaces with a single one without recurring REPLACE calls.
I don't have it handy, but it goes like this:
DECLARE @ReplaceChar NCHAR(1)
SET @ReplaceChar = CHAR(7) -- any character you sure is not mentioned anywhere in the strings
SELECT s.Sometext, REPLACE(REPLACE(REPLACE(s.Sometext, ' ', ' ' + @ReplaceChar), @ReplaceChar + ' ', ''), @ReplaceChar, '')
FROM #Sample s
_____________
Code for TallyGenerator
August 24, 2016 at 7:35 pm
And you probably want to replace CR+LF with a space at the very beginning, as "SET NOCOUNT ON" for SQL Server is the same as
SET
NOCOUNT
ON
CREATE TABLE #Sample(
Sometext varchar(5000)
);
INSERT INTO #Sample
VALUES
('CREATE PROCEDURE Proc1 AS SELECT * FROM sys.tables'),
('CREATE PROCEDURE Proc2 AS
SET
NOCOUNT
ON
SELECT * FROM sys.tables'),
('CREATE PROCEDURE Proc2 AS SET NOCOUNT ON SELECT * FROM sys.tables'),
('CREATE PROCEDURE Proc3 AS SET ' + replicate (' ', 1800) + ' NOCOUNT ON SELECT * FROM sys.tables');
DECLARE @ReplaceChar NCHAR(1)
SET @ReplaceChar = CHAR(7)
SELECT s.Sometext, REPLACE(REPLACE(REPLACE(REPLACE(s.Sometext, CHAR(13)+CHAR(10), ' '), ' ', ' ' + @ReplaceChar), @ReplaceChar + ' ', ''), @ReplaceChar, '')
FROM #Sample s
WHERE REPLACE(
REPLACE(
REPLACE(
REPLACE(s.Sometext, CHAR(13)+CHAR(10), ' '
), ' ', ' ' + @ReplaceChar
), @ReplaceChar + ' ', ''
), @ReplaceChar, ''
) LIKE '%SET NOCOUNT ON%'
_____________
Code for TallyGenerator
August 24, 2016 at 7:41 pm
And TABs too
_____________
Code for TallyGenerator
August 24, 2016 at 8:20 pm
Sergiy (8/24/2016)
There was an article from Jeff Moden how to replace any number of consecutive spaces with a single one without recurring REPLACE calls.I don't have it handy, but it goes like this:
DECLARE @ReplaceChar NCHAR(1)SET @ReplaceChar = CHAR(7) -- any character you sure is not mentioned anywhere in the strings
SELECT s.Sometext, REPLACE(REPLACE(REPLACE(s.Sometext, ' ', ' ' + @ReplaceChar), @ReplaceChar + ' ', ''), @ReplaceChar, '')
FROM #Sample s
The code that I included is from the discussion from that article. It's mentioned to be the fastest method proposed.
August 24, 2016 at 8:43 pm
This code would make a handy little inline table valued function.
It currently filters out everything but letters and numbers.
Fairly quick, too.
declare @find varchar(50) = 'SET NOCOUNT ON'
declare @txt varchar(max) =
'Come and listen to my story bout a man named Jed
A poor mountaineer barely kept his family fed SET
NOCOUNT ON And then one day he was
shootin at some food, when up through the ground come
a-bubblin crude.';
set statistics time on;
with test as (select top(len(@txt)) ''+substring(@txt,N,1) as txtchar
from vtally)
SELECT 'EXISTS'
WHERE (select ''+txtchar
from test
where txtchar BETWEEN 'A' and 'Z'
or txtchar BETWEEN '0' and '9'
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)')
LIKE '%'+REPLACE(@find,Space(1),Space(0))+'%'
set statistics time off;
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2016 at 9:38 pm
Luis Cazares (8/24/2016)
Sergiy (8/24/2016)
There was an article from Jeff Moden how to replace any number of consecutive spaces with a single one without recurring REPLACE calls.I don't have it handy, but it goes like this:
DECLARE @ReplaceChar NCHAR(1)SET @ReplaceChar = CHAR(7) -- any character you sure is not mentioned anywhere in the strings
SELECT s.Sometext, REPLACE(REPLACE(REPLACE(s.Sometext, ' ', ' ' + @ReplaceChar), @ReplaceChar + ' ', ''), @ReplaceChar, '')
FROM #Sample s
The code that I included is from the discussion from that article. It's mentioned to be the fastest method proposed.
Speed goes after functional correctness.
If some script has space padding at the end of its lines (and I personally saw insane numbers of spaces in lines of code) then your script will fail to deliver.
And nobody cares how quickly you get a wrong result.
_____________
Code for TallyGenerator
August 24, 2016 at 9:51 pm
Another unorthodox method to get rid of repeating spaces:
SELECT s.Sometext,
(
SELECT ' ' + LTRIM(RTRIM(Item))
FROM dbo.Split(Sometext, ' ')
WHERE Item > ''
FOR XML PATH(''), TYPE
).value ('./text()[1]', 'varchar(max)')
FROM #Sample s
_____________
Code for TallyGenerator
August 25, 2016 at 9:50 am
Rather than literal strings of spaces, I recommend using SPACE(), just for readability:
/*CREATE FUNCTION ...*/
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Text)),
SPACE(33), ' '),
SPACE(17), ' '),
SPACE(9), ' '),
SPACE(5), ' '),
SPACE(3), ' '),
SPACE(2), ' '),
SPACE(2), ' ') AS NewText
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".
August 25, 2016 at 12:25 pm
ScottPletcher (8/25/2016)
Rather than literal strings of spaces, I recommend using SPACE(), just for readability:
/*CREATE FUNCTION ...*/
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Text)),
SPACE(33), ' '),
SPACE(17), ' '),
SPACE(9), ' '),
SPACE(5), ' '),
SPACE(3), ' '),
SPACE(2), ' '),
SPACE(2), ' ') AS NewText
+1
Excuse me, I need to go re-write some code.
-- Itzik Ben-Gan 2001
August 25, 2016 at 1:18 pm
But isn't the problem with just replacing spaces is that it doesn't deal with other special characters?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2016 at 1:27 pm
The Dixie Flatline (8/25/2016)
But isn't the problem with just replacing spaces is that it doesn't deal with other special characters?
It depends on what you're doing.
August 25, 2016 at 1:30 pm
Luis Cazares (8/25/2016)
The Dixie Flatline (8/25/2016)
But isn't the problem with just replacing spaces is that it doesn't deal with other special characters?It depends on what you're doing.
My strategy (which varies depending on the data) is to replace CHAR(9), CHAR(10), CHAR(13), CHAR(160) (and a couple others I can't think of at the moment) with spaces. Then do the "replace duplicate spaces with one" trick that Luis showed and Scott improved on.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply