A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


T-SQL Word Count Function

 

 
 
Function

create function f_count_word_in_string (@field varchar(max), @word varchar(max))

returns int
as
begin

   return (LEN(@field) - LEN(REPLACE(@field, @word,'')))/LEN(@word)
end
 
 
 

 Test:

select dbo.f_count_word_in_string('tester

I want to test this, but how?  

oh tester tester', 'tester')

 
 Result:
 
 
 

SQL Server: Store localized versions of strings with language specific full text search

CREATE TABLE [dbo].[LanguageStrings](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [String] [xml] NOT NULL,

 

I added a full text index on the String column without specifying a language for the index. I then inserted the following into it.

 

INSERT INTO LanguageStrings VALUES

(

       '<strings>

              <string xml:lang="en">run</string>

              <string xml:lang="es">correr</string>

       </strings>'

)

 

The following query returns a row as expected.

 

select * from LanguageStrings where contains(String, 'formsof(inflectional, "run")', language 'English')

 

The past tense “ran” also returns a row proving that the inflectional search is working on the index.

 

select * from LanguageStrings where contains(String, 'formsof(inflectional, "ran")', language 'English')

 

If I search for “ran”, but tell the search to use Spanish as the language it returns no rows proving that it’s only searching the xml elements that are marked with the language specified.

 

select * from LanguageStrings where contains(String, 'formsof(inflectional, "ran")', language 'Spanish')

 

If I search for “correr” using Spanish as the language it returns a row.

 

select * from LanguageStrings where contains(String, 'formsof(inflectional, "correr")', language 'Spanish')

 

And if I search for the past tense “corrió” it also returns a row proving that inflectional search is also properly working for Spanish.

 

select * from LanguageStrings where contains(String, 'formsof(inflectional, "corrió")', language 'Spanish')

SQL SERVER MSSMS Error "Saving Changes is not Permitted."

 

 
 
 
 
 
   To fix this error in SSMS: At the top right of your studio go to Tools > Options.
 
 
 
Click on Designers and uncheck "Prevent Saving Changes that require table re-creation".
FYI, you should understand the implications of dropping and recreating your tables and this isn't always the best option for making table alterations.