can we find 3 letter word in the column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

can we find 3 letter word in the column

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-16 : 03:51:16

Hi friends, i have a typical requirement. Can we find the which 3 letter combination is mostly repeating in the data?
I have table with 2 column, firstname and lastname

FName Lname

Mark Thefe
Satish Gorijal
arjun gupta
ronal teupta
satih kerbo
suha dergupta

From the above table, with out knowing any thing, i want to know, which 3 letter combination is repeating mostly from lastname column. I dont know, what are those 3 letters? For example from above table i can say "upt" repeat 3 times. like this can we find what are all those repeated combinatinos and its count?
Can we find this through query?

G. Satish

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 03:53:24

3 consecutive letters ? in any positions ? any other rules ?

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-16 : 03:55:41

yes, 3 consecutive letters, at any postion.

quote:
Originally posted by khtan

3 consecutive letters ? in any positions ? any other rules ?

KH
[spoiler]Time is always against us[/spoiler]

G. Satish

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 03:57:35

[code]
declare @sample table
(
FName varchar(10),
LName varchar(10)
)
insert into @sample
select 'Mark', 'Thefe' union all
select 'Satish', 'Gorijal' union all
select 'arjun', 'gupta' union all
select 'ronal', 'teupta' union all
select 'satih', 'kerbo' union all
select 'suha', 'dergupta'

select word = lower(substring(LName, n.NUMBER, 3)), count(*), rank() over (order by count(*) desc)
from @sample s
cross join F_TABLE_NUMBER_RANGE(1, 100) n
where len(substring(LName, n.NUMBER, 3)) = 3
group by lower(substring(LName, n.NUMBER, 3))
order by count(*) desc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-16 : 04:04:17

Thank you..its working fine. If i want Fname, Lname, word, count as output..how can i do this?

quote:

Originally posted by khtan


declare @sample table
(
	FName 	varchar(10),
	LName	varchar(10)
)
insert into @sample
select	'Mark',   'Thefe'	union all
select	'Satish', 'Gorijal'	union all
select	'arjun',  'gupta'	union all
select	'ronal',  'teupta'	union all
select	'satih',  'kerbo'	union all
select	'suha',   'dergupta'

select	word = lower(substring(LName, n.NUMBER, 3)), count(*), rank() over (order by count(*) desc)
from	@sample s
	cross join F_TABLE_NUMBER_RANGE(1, 100) n
where	len(substring(LName, n.NUMBER, 3)) = 3
group by lower(substring(LName, n.NUMBER, 3))
order by count(*) desc

KH
[spoiler]Time is always against us[/spoiler]

G. Satish

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 04:11:49

[code]
select FName, LName, Word, WordCount = count(*) over (partition by Word)
from
(
select FName, LName,
Word = lower(substring(LName, n.NUMBER, 3))
from @sample s
cross join F_TABLE_NUMBER_RANGE(1, 100) n
where len(substring(LName, n.NUMBER, 3)) = 3
) s
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 04:16:08

another method using CROSS APPLY


select	FName, LName, Word, WordCount = count(*) over (partition by Word)
from	@sample s
	cross apply
	(
		select	Word = lower(substring(LName, n.NUMBER, 3))
		from	F_TABLE_NUMBER_RANGE(1, len(s.LName) - 2) n
                where	len(substring(LName, n.NUMBER, 3)) = 3
	) w
order by WordCount desc, Word

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-16 : 06:21:04

HI Khtan,

Thanks for your reply. Its working fine. But it showing results including sinle and double letter words also. I only need 3 consecutive letter combinations only.

Friend, i need one more help. I can work by using this query and my task will completed. But i didn;t understand what's the internal functionality of ur query . how it works and mainly about that funciton "F_TABLE_NUMBER_RANGE". I saw a link about this function.But i didn;t understand. Can you please explain this query breifly..
Thanking you

quote:

Originally posted by khtan


select	FName, LName, Word, WordCount = count(*) over (partition by Word)
from
(
	select	FName, LName, 
		Word = lower(substring(LName, n.NUMBER, 3))
	from	@sample s
		cross join F_TABLE_NUMBER_RANGE(1, 100) n
	where	len(substring(LName, n.NUMBER, 3)) = 3
) s

KH
[spoiler]Time is always against us[/spoiler]

G. Satish

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 06:54:47

i don't see any single or double letter word in there. Can post the sample data and the query that you used ?

I am using F_TABLE_NUMBER_RANGE function to generate a series of numbers for substring(). If you have a number table you can use that also. Alternatively you can also use master..spt_values as number table.

KH
[spoiler]Time is always against us[/spoiler]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-16 : 07:04:16

Khtan, i have lakhs of records in my table. i given only few rows for sample. When i run the query. i am getting data in word column as single letter, double and three letters. And also, in my table it has only 4 lakh recorsd. but when i run this query, i am getting 25 laksh records as result. My query is as below.

select LName, Word, Culture, WordCount = count(*) over (partition by Word)
from
(
select LName, Culture, Word = lower(substring(Name, n.NUMBER, 3))
from TBL_LastName s cross join F_TABLE_NUMBER_RANGE(1, 100) n
where len(substring(LName, n.NUMBER, 3)) = 3
) s

BRUNO ALVES a Port 4
VITOR ALVES a Port 4
RODRIGO ALVIM a HISP 4
RODRIGO ARROZ a HISP 4
JORGE BAPTISTA b Port 3
JOAO BOTELHO b Port 3
RUI BORGES b Port 3
RICARDO CHAVES c Port 1
RICARDO FERNANDES f Port 5
VITOR FROIS f Port 5

I tried this also....but no use

select LName, Word, Culture, WordCount = count(*) over (partition by Word)
from
(
select LName, Culture, Word = lower(substring(Name, n.NUMBER, 3))
from TBL_LastName s cross join F_TABLE_NUMBER_RANGE(1, 100) n
where len(substring(LName, n.NUMBER, 3)) = 3
) s Where len(word) = 3

G. Satish

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-16 : 07:07:21

Please post the sample data produce word with 2 or 3 letters

KH
[spoiler]Time is always against us[/spoiler]

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 19:51:51

quote:
Originally posted by satish.gorijala

Hi friends, i have a typical requirement. Can we find the which 3 letter combination is mostly repeating in the data?

I've just gotta ask... other than insatiable curiosity, why do you need to do this? What are the business reasons the require such logic?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources