Assemble Full Text Catalog Search String
I have a field in a database that I need to assemble a string for (I'm not great with advanced string operations in t-sql).
What the Field Contains: John R. Smith Holding Company
The string I'd like to assemble for a full text catalog search:
"John" AND "Smith" AND "Holding" AND "Company"
Thanks!
What the Field Contains: John R. Smith Holding Company
The string I'd like to assemble for a full text catalog search:
"John" AND "Smith" AND "Holding" AND "Company"
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I could have sworn the author was asking a question related to the use of Full-Text Search and in particular the CONTAINS clause. I guess I was mistaken.
CREATE FUNCTION [dbo].[Split] (@separator VARCHAR(32), @string VARCHAR(MAX))
RETURNS @t TABLE
(
val VARCHAR(MAX)
)
AS
BEGIN
DECLARE @xml XML
SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'
INSERT INTO @t(val)
SELECT r.value('.','VARCHAR(5)') as Item
FROM @xml.nodes('//root/r') AS RECORDS(r)
RETURN
END
e.g. SELECT * FROM dbo.Split(' ', 'John R. Smith Holding Company')
You can store the results of the above into a temporary table. Remove any invalid characters using the REPLACE function and any invalid rows using DELETE and then combine them using something like this:
declare @Str varchar(1000)
set @Str = ''
select @Str = @Str + CASE WHEN LEN(@Str) = 0 THEN '"' ELSE '" AND "' END + ColumnName from TableName
set @Str = @Str + '"'