Character Replace?
Nov 14, 2006Hi,
lets say i have a table and having some varchar columns. What i wanna do is, to change "ý" character with "I" character in those columns.
Can anyone help me?
ADVERTISEMENT
T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character
Aug 6, 2015I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And I need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
Replace, The ' Character, And Yea...
Aug 22, 2007I have the following:----------------- WHILE PATINDEX('%,%',@Columns)<> 0 BEGINSELECT @Separator_position = PATINDEX('%,%',@Columns)SELECT @array_Value = LEFT(@Columns, @separator_position - 1)SET @FieldTypeID = (SELECT FieldTypeID FROM [Form].[Fields] WHERE FieldID = (CAST(@array_Value AS INT)))SET @FieldName = (SELECT [Name] FROM [Form].[Fields] WHERE FieldID = @array_Value)print 'arry value' + CONVERT(VarChar(500), @array_value)print 'FieldTypeID: ' + CONVERT(VARCHAR(500), @FieldTypeID)PRINT 'FieldName: ' + @FieldNameBEGINIF @FieldTypeID = 1 OR @FieldTypeID = 2 OR @FieldTypeID = 3 OR @FieldTypeID = 9 OR @FieldTypeID = 10 OR @FieldTypeID = 7BEGINSET @InnerItemSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @array_Value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 4 OR @FieldTypeID = 8 --DropDownList/RadioButtonlistBEGINSET @InnerItemSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 5 --CascadingBEGINSET @InnerItemSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeiD = 6 --ListBoxBEGINSET @InnerItemSelect = ' (SELECT i.[CSV] FROM @ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT it.[CSV] FROM @TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FieldTypeID = 11 --UsersBEGINSET @InnerItemSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDELSE IF @FIelDTypeID = 12 --GroupBEGINSET @InnerItemSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'SET @InnerTaskSelect = ' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @array_value + ') AS ''' + @FieldName + ''' 'ENDENDPRINT 'Inner Item Select:' + @InnerItemSelectPRINT 'Inner Task Select:' + @InnerTaskSelectSET @IDSelect = @IDSelect + @InnerItemSelect + ', 'SET @TSelect = @TSelect + @InnerTaskSelect + ', 'SELECT @Columns = STUFF(@Columns, 1, @separator_position, '')END --------------- That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.THe problem I have is @FieldName might be: ryan's field. That apostrophe is killing me because the SQL keeps it as ryan's field, not ryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output? Thanks.
View 4 Replies View RelatedReplace Last Character
Nov 19, 2014i would like to replace the last character of one column to *here is the code:
update table set right(column, 1) = '*'
where WORKFLOW_ID = '2'
Replace Special Character
Feb 3, 2006I'm trying to remove the special character ÿ from a varchar
select replace(my_col,'ÿ',' ')
this works, but the problem is that it also seems to replace the 'normal' y
(Database is case sensitive)
Why is that?
Replace Specific Character
Aug 14, 2007I have to replace 'MED-40'instead of '30' in an incidentno column of incidents table ,the fiels is varchar(12),now the problem is it is replacing whereever it sees '30' while I want only the leftmost to be replaced for eg
Let '301530' be the incidentno to be replaced
if I run
UPDATE incidents_mm set incidentno = replace(incidentno, '30', 'MD-40')where incidentno= '301530'
query i get the ans given below
'MD-4015MD-40'
and I just want the ans to be
MD-401530
In short i just want first 2 characters to be replaced is there any way i can do that
Thanks in adv
Replace A Character Inside A Field
Mar 24, 2008Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19
So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes
How can I do this? Any help?
Thanks
T-SQL (SS2K8) :: Replace A Special Character In Results?
Jun 11, 2014I have an database that is housing a path used to locate an external file. This application was written many years ago and I am now trying to bring the files into the database as a VARBINARY.
The table is holding the path like this "/folder/folder/file"
I am trying to convert that path to "folderfolderfile"
In my Select statement I have
SELECT ProdID, REPLACE (PATH, /, ) FROM dbo.blahblah
The problem is that I can't figure out to make SQL understand that "/" is the character I want to replace.
Find And Replace Carriage Return Character
May 30, 2007Hi I have a text file which I need to import into Access or SQL.
It is 500,000 records which is pipe delimited. The problem is, is that it can contain carriage returns: (square symbol).
Therefore I need to find and replace these characters
Does anybody know of any free ware text file viewers that can do this??
Best Regareds
David
T-SQL (SS2K8) :: Replace Multiple Characters With Single Character In A Column?
Jun 21, 2012I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times
May 17, 2015I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
Select Part Of Character String Based On A Character
Apr 15, 2004I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
Return Left-most Character From 8 Character String
Oct 1, 2014I'd like to return the left-most character from an 8 character string & the third from the left character too.
Like this ABC00123 returns AC
$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());
echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){
[Code] ....
Read Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character
Feb 1, 2008Hi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());
SQL Replace '-' By ''
Oct 16, 2007Hi all i have a question regarding sql, i want to replace some characters...
any knows simply how to do this?
I want to replace "999-25000-69" by "9992500069"
grtz
Replace?
Nov 28, 2006i got a 100k rows column contain first name + last name. but half of them are got comma between first and last name. how can i update and remove all the comma. can anyone provide a statment please thanks so much
View 2 Replies View RelatedReplace
Aug 15, 2006can I use a replace on text type field?
View 1 Replies View RelatedReplace '
Apr 11, 2006hi
when i execute ...
select * from Members where address = '6257 Rockwell's'
ERROR :
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '
'.
Kindly help me how to solve '
Replace Roa
Nov 15, 2006i just wanna replace the last word 'roa' to 'road'
what is wrong with my code. I dont relaly see any problem. But it's replaceing all the 'roa' to 'road'.
declare @table table(ad_str1 varchar(20))
insert @table
select 'street road roa' union all
select 'street street'
select ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) from @table
update @table
set ad_str1 = replace(ad_str1, ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))), 'road')
where ltrim(right(ad_str1, charindex(' ', reverse(ad_str1)))) = 'roa'
select * from @table
Using Replace
Nov 29, 2007I have a colunm in a table that has a list of email address.
I want to remove a certain email address from that colunm, but it is in 54 different rows. What is the best way to do this ?
How To Replace Having
Dec 12, 2007Dear all,
here is one query,
IF EXISTS (SELECT SUM(DEBIT_AMOUNT) FROM @Ledger
HAVING SUM(DEBIT_AMOUNT)<>SUM(CREDIT_AMOUNT))
i'm trying to tuning myprocedures. i think having is not good enough to use.
how can we replace having in this case.....
thank you very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
Using 'IN' To Replace 'EXCEPT'
May 28, 2008Hi everyone,
I have wriiten a query that works great in sql server but MS Access does not support EXCEPT. I have to rewrite this query using 'IN' or 'NOT IN' but can not figure it out because it must be compared on all fields. I'm sure this will require multiple subqueries because 'IN' must return only one field but I can not rap my brain around it. Could someone please help with this? Here is the 'EXCEPT' query I know works.
Code Snippet
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE ac.INDIVIDUAL_ID = 5249
Except
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE ((ac.participation_id <> 101) AND (sh.share_type > 49 )) AND (ac.Individual_Id = 5249); This code gets me close using 'IN'.
Code Snippet
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
sh.Balance, sh.Available_Bal FROM
(((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
Participation_Type ON ac.Participation_Id = Participation_Type.ID)
WHERE (ac.INDIVIDUAL_ID = 5249) AND sh.member_nbr not in
(SELECT sh.member_nbr from Share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49) AND sh.account_nbr not in
(SELECT sh.account_nbr from share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49)
Find And Replace
Sep 13, 2006To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.
View 1 Replies View RelatedReplace &<br&> Tag In CRLF
Feb 18, 2007Hi all,
I made a migration operation and when I am looking inside database I can see the <BR> tags.
The question is how to write a query that replace all of this occurances of <BR> inside the colum table, with CLRF which is a new line code.
Thanks in advance.
How To Do REPLACE In SQL Queries
Sep 23, 2007Hi,
I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ...
I have this stored in a column IMAGESRC
Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg I need to change it to this
Project/NEWUSERID/ProjectImages/702d_2.jpg
How can I accomplish this in SQL???
thanks in Advance
Dollarjunkie
Replace Function In SQL?
Oct 30, 2003Hi,
I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name.
Thanks
REPLACE Used In Query
Feb 21, 2004I'm not sure how to use REPLACE here. The Query following REPLACE returns a string whose format is A B C D and I'm trying to convert it to 'A','B','C','D' I'm doing something wrong because query analyzer doesn't like something about the way I've written this.
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''')
Entire SPROC
-------------------------------------------------------------------------------------------------
CREATE PROCEDURE _premium_BSH (@Portfolio_Name NVarChar (50), @UserID int, @Symbol VarChar (1500)) AS
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @UserID) AND (Portfolio_Name = @Portfolio_Name),'''','''''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal
GO
Find And Replace.
Apr 30, 2004Does anybody know how to search through all stored procedures (in SQL Server 2000) and find and replace text without having to open each one individually in Query Analyzer and doing so one at a time?
That would be so time consuming. I want to be able to change my table names, but I have so many stored procedures allready using the old names. To go and find each name and replacing them is a task I don't want to even try and do.
Thank you to whomever can help.
Alec
Using Replace Function
Aug 10, 2004I am using MSDE. In this I have a field that contains a desciption. Prior to saving to the db, I replace all vbcrlf's to <br />'s. That works fine when displaying in HTML, but when I display it in a datagrid, I want to replace the <br />'s with vbcrlf's.
I thought I might be able to do it with a replace function in my SQL query, something like "Select ID, replace(Description, '<br />', vbcrlf) as Description". This produces an error that vbcrlf is not a fieldname. So I tried "select replace(Description, '<br />', 'xxx') as Description", and while this did not create an error, neither was the text replaced.
Am I not using the replace function correctly? Is there another way in which I might accomplish the task?
Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database=dbname"
Dim CommandText As String
'Command text is greatly abreviated for this discussion.
CommandText = "Select ID, replace(Description, '<br />', vbcrlf) as Description"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet()
Dim dv as new dataview()
myCommand.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
...
...
...
SQL To Replace String
Jul 26, 2002Hi,
I need to write a single replace sql as folows:
I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'
I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'
any help would be appreciated
Thanks
SQL Replace Apostrophe
Apr 18, 2000Hello
Can anyone tell me how to replace a single apostrophe in a record with a double apostrophe (in Query Analyzer, SQL7)? I've tried "select replace("d'","d'","d''")FROM RESORT" but it doesn't UPDATE the table.
Suggestions gratefully received!
Mark
SQL To Replace String
Jul 26, 2002Hi,
I need to write a single replace sql as folows:
I have a string consisting of numbers seperated by a space. Some of the numbers are suffixed with a star like this: '1 12* 5 7*'
I need to remove those numbers that are suffixed with a star. In other words, I need an output as follows: '1 5'
any help would be appreciated
Thanks
Using Like Function With Replace
Mar 28, 2006I have a need to evaluate a parameter to use like keywords. (Not my server, so indexing is not available.) I can get the first scenario to work, but wanted to see about getting the syntax so I wouldn't have to exec the statement. If I run the first execute statement, I get three rows (accurate). If I run the second statement, I get no rows. Anybody help me out with the syntax? I've tried so many variations, I'm lost. Many thanks.
declare @key as varChar(50), @sql as varchar(1000)
set @key='flow afow'
set @sql='select myID from myTable where [title] like ''%' + replace(rtrim(ltrim(@key)),' ','%'' or [title] like ''%') + '%'''
exec(@sql)
select myID from myTable where [title] like '%' + replace(@key,' ', '%''' or [title] like '''%') + '%'