Tomaz.tsql

Split string with sys.dm_fts_parser

In SQL 2008+ one can now very easy and straightforward split words in an array of strings as  following:

-- Create table

create table MyTable

(id int

,text varchar(500)

)

-- Insert some staging data

insert into MyTable

select 1 as id

,'You can write me an email' as text union all

select 2 as id

,'on my private email address: tomaz.tsql@gmail.com' as text union all

select 3 as id

,'or even on this imaginary email address tomaz.tsql@nowhere.at' as text union all

select 4 as id

,'or on my fake non-existing email address not-exist@all'

--Return all words

select

display_term as words

from myTable

cross apply sys.dm_fts_parser('"' + [text] + '"',0,0,0)

group by display_term

--Return only valid email addresses

select

display_term as email

,count(*) as Nof_emails

from myTable

cross apply sys.dm_fts_parser('"' + [text] + '"',0,0,0)

group by display_term

having display_term like '%@%'

order by Nof_emails desc

 

Comments

No Comments