SQL help - how do we find that a particular string appears x times in a certain field

Question

Post reply

SQL help - how do we find that a particular string appears x times in a certain field

mw_sql_developer

SSCoach

Points: 19445
More actions
August 25, 2016 at 12:17 pm

#320919

The field "content" in the table "catalog" can have this particular string "<DataSourceReference>" appear more than once.
I am interested in an expression in the SELECT statement that tells me that this particular string appears x times ?
How can we do that ?
Next Question: I would like if you could also tell me how to extract the n th substring.
I mean if that string appears more than once I would like to extract the first occurrence as well as the second occurrence + the characters that go past it.
BTW- THE SQL below just extracts the first occurrence of a sub-string from the field "Content". Please ignore
Select DISTINCT
CASE
WHEN CHARINDEX('<DataSourceReference>', (CONVERT(varchar(max),convert(varbinary(max),content)))) > 0 THEN
LEFT( SUBSTRING( (CONVERT(varchar(max),convert(varbinary(max),content))) , CHARINDEX('<DataSourceReference>', (CONVERT(varchar(max),convert(varbinary(max),content)))) + 21 , 50 ) , CHARINDEX('<', (SUBSTRING( (CONVERT(varchar(max),convert(varbinary(max),content))) , CHARINDEX('<DataSourceReference>', (CONVERT(varchar(max),convert(varbinary(max),content)))) + 21 , 50 )) )-1 )
WHEN CHARINDEX('DataSource Name=', (CONVERT(varchar(max),convert(varbinary(max),content)))) > 0 THEN
LEFT( SUBSTRING( (CONVERT(varchar(max),convert(varbinary(max),content))) , CHARINDEX('DataSource Name=', (CONVERT(varchar(max),convert(varbinary(max),content)))) + 17 , 50 ) , CHARINDEX('>', (SUBSTRING( (CONVERT(varchar(max),convert(varbinary(max),content))) , CHARINDEX('DataSource Name=', (CONVERT(varchar(max),convert(varbinary(max),content)))) + 17 , 50 )) )-2 )
ELSE NULL
END as DataSource1
FROM
MHPAPP1.ReportServer.dbo.Catalog
WHERE
TYPE = 2
ORDER BY 1

Viewing 15 posts - 1 through 15 (of 29 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

please provide table script and sample data that describes your problem.

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

I can not .. Why ? The Catalog table is a table in the ReportServer.

I am sure if you do have SQLserver , the go the ReportServer database and then you will find the Catalog table.

Otherwise, let me write a script and post it here

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

if object_id('tempdb..#t') is not null DROP TABLE #t;

create table #t( str varchar(4000) );

insert into #t(str) values ( 'fox' );

insert into #t(str) values ( 'fox fox fox' );

insert into #t(str) values ( 'fox abc fox ' );

insert into #t(str) values ( 'fox abc frht fox fox ' );

-- I need a SELECT statement that shows how many times the word fox appears

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

mw112009 (8/25/2016)
if object_id('tempdb..#t') is not null DROP TABLE #t;
create table #t( str varchar(4000) );
insert into #t(str) values ( 'fox' );
insert into #t(str) values ( 'fox fox fox' );
insert into #t(str) values ( 'fox abc fox ' );
insert into #t(str) values ( 'fox abc frht fox fox ' );
-- I need a SELECT statement that shows how many times the word fox appears

so what is the expected result ....9?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

I mean in each row how many times does the word fox appear ?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

mw112009 (8/25/2016)
I mean in each row how many times does the word fox appear ?

Using a tally table, SUBSTRING(bigstring, 'fox', n) or whatever.

Work it out from there. You'll need to aggregate by bigstring and count rows.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Maybe something like this:

SELECT *, (LEN(str) - LEN(REPLACE(str, 'fox', '')) ) / 3

FROM #t;

But I'm not sure if it causes a problem with the result. What would you expect if we add this?

insert into #t(str) values ( 'foxfox' );

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

mw_sql_developer SSCoach Points: 19445 More actions · Answer 8

Select EXPR(str) FROM #t;

Output should be as follows

1

3

2

3

mw_sql_developer SSCoach Points: 19445 More actions · Answer 9

Thx Luis

But I had to change it as follows to take care of any string

if object_id('tempdb..#t') is not null DROP TABLE #t;

create table #t( str varchar(4000) );

insert into #t(str) values ( 'rabbit ' );

insert into #t(str) values ( 'rabbit rabbit rabbit ' );

insert into #t(str) values ( 'rabbit abc rabbit ' );

insert into #t(str) values ( 'rabbit abc frht rabbit rabbit ' );

SELECT *, (LEN(str) - LEN(REPLACE(str, 'rabbit', '')) ) / LEN('rabbit')

FROM #t;

mw_sql_developer SSCoach Points: 19445 More actions · Answer 10

Thx but that is not really the challenge...

What I need is an expression that will give me the 3 letters that follow the nth occurrence of the key word if it exists..

I mean see the code below, I need the 3 letters that follow the 2nd occurrence of the word <ConnectString>

mw_sql_developer SSCoach Points: 19445 More actions · Answer 11

Sorry forgot to paste the code in my previous post, so here it is. What I meant was after the word <DataReference>

if object_id('tempdb..#t') is not null DROP TABLE #t;

create table #t( str varchar(4000) );

insert into #t(str) values ( '<DataReference>ABCGT ' );

insert into #t(str) values ( '<DataReference>UITR <DataReference>ACDE <DataReference>ERR ' );

insert into #t(str) values ( '<DataReference>ABC <DataReference>GHTY ' );

insert into #t(str) values ( '<DataReference>xyz frht <DataReference>RTE <DataReference>ZZZ ' );

Luis Cazares SSC Guru Points: 183698 More actions · Answer 12

Maybe this could help:

if object_id('tempdb..#t') is not null DROP TABLE #t;

create table #t( str varchar(4000) );

insert into #t(str) values ( '<DataReference>ABCGT ' );

insert into #t(str) values ( '<DataReference>UITR <DataReference>ACDE <DataReference>ERR ' );

insert into #t(str) values ( '<DataReference>ABC <DataReference>GHTY ' );

insert into #t(str) values ( '<DataReference>xyz frht <DataReference>RTE <DataReference>ZZZ ' );

SELECT *

FROM #t

CROSS APPLY( SELECT STUFF(REPLACE( str, '<DataReference>', CHAR(7)), 1, CASE WHEN str LIKE '<DataReference>%' THEN 1 ELSE 0 END, '') )x(NewStr)

CROSS APPLY dbo.DelimitedSplit8k(x.NewStr, CHAR(7))s;

The DelimitedSplit8K can be obtained here along with the explanation: http://www.sqlservercentral.com/articles/Tally+Table/72993/

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 13

mw112009 (8/25/2016)
Thx but that is not really the challenge...
What I need is an expression that will give me the 3 letters that follow the nth occurrence of the key word if it exists..
I mean see the code below, I need the 3 letters that follow the 2nd occurrence of the word <ConnectString>

Tally table gives you n.

Then use substring or LEFT to choose your characters.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

I'm sorry if I am taking the fun out of building a SQL only solution, but there are lots of basic samples of SQLCLR showing how to expose the Regex.Matches() method from .NET CLR.

A good evrsion is located here courtesy of Phil Factor:

https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/[/url]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?