Using Replace Function With 2 Keywords To Look For
Oct 16, 2007
Hi,
We have this syntax from Microsoft for Replace function:
REPLACE ( string_expression1 , string_expression2 , string_expression3 )
I want to search for two keywords and not only one:
images and images/ (second one is with back slash.
Is it possible to include something like:
images/ OR images in expression2 like
REPLACE ( string_expression1 ,'images/ OR images', string_expression3 )
I want something like that, how do I get that please?
Thank you.
View 4 Replies
ADVERTISEMENT
Sep 14, 1998
I installed SQL server 7.0 beta 3 and I have been experimenting with the full text search capabilities. The catalogs appear to build correctly, but when I issue a query against the full text database, isql/w always returns with `contains` is not a recognized function name. I am confident of my query syntax, since I can execute an identical query against a database without full text search enabled and I get an error stating that the target database does not have full text searching enabled. Has anybody else encountered this? I am hoping that I am missing something obvious/simple.
View 2 Replies
View Related
Oct 30, 2003
Hi,
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
View 9 Replies
View Related
Aug 10, 2004
I 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()
...
...
...
View 3 Replies
View Related
Mar 28, 2006
I 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 '''%') + '%'
View 2 Replies
View Related
May 17, 2002
I'm trying to replace some text in a field. It looks something like this:
63.73 Avail %= 36.27 Used space MB = 2609.34375 Free space MB = 1485.34765625
I only want the 63.73 number at the beginning. How do I put a wildcard to replace everything after it, starting with 'Avail...'?
View 4 Replies
View Related
May 12, 2004
I want to perform Replace(searchstring, oldstring, newstring) like function in T-sql as same as in VB..
is there existing one or any other user-defined function?
thanks...
View 2 Replies
View Related
Apr 16, 2007
Is it possible to replace 4 different values into one alias column?
ie REPLACE(EligStatus, '0', 'Verified') AS VRFD
..now I need to replace 3 other values with words but i want to keep them in the same VRFD column so I can add it to the report layout.
or is there a better way to do it?
I can't set up a linking table because nowhere in the DB are the values linked to the actual status.
Thanks in advance,
Mark.
View 4 Replies
View Related
Aug 12, 2004
Let's try this again.
When I try to use the replace function in my query it ignores the "<" and ">" characters, so that this query:select Description, replace(Description, '<', '* * *') as [Description 1] , replace(Description, 'br /', '? ? ?') as [Description 2] from TT_Projects
produces these results:
Description | Description 1 | Description 2
testsfv <br /> | testsfv <br /> | testsfv <? ? ?> I also tried to replace char(60) with no success.
Any ideas why this is happening?
View 1 Replies
View Related
Oct 19, 2005
Hi,
I posted a request here and am still working on it when I landed on this bug.
select top 10 replace(comma_separated_string,',','giveaverylongp assagehere') from table
The function works fine if the comma separated string is small or if the passage is small. It fails for long passages..
Is this a mssql bug?
View 1 Replies
View Related
Oct 15, 2007
how should i use replace function in sql statement??
in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.
my stored procedure accepts two parameters.
Is there any way to solve my problem??
Jaimin
View 4 Replies
View Related
Apr 9, 2007
Hi,Please find the below scenarioOriginal Tablecourse branch_existBY0UI1PO1LI0MK1select REPLACE(branch_exist,1,'yes') as branch from university;displayscourse branch_existBY0UIYesPOYesLI0MKYesWhat i need iscourse branch_existBYNoUIYesPOYesLINoMKYesSql-Server replace function only accepts three arguments, anysuggestions will be greatly welcomed!
View 3 Replies
View Related
May 20, 2008
I have 2 tables
1) One With ExchangeRates
a. CurrencyCode ValidFromPeriod Rate
b. EX1 200801 1
c. EX1 200803 2
d. EX1 200805 3
2) One with just OrderHeaders
a. ID CurrencyCode Period
b. 1 EX1 200801
c. 2 EX1 200802
d. 3 EX1 200803
e. 4 EX1 200804
f. 5 EX1 200805
The Idea is to link these orderheaders with their correct ExchangeRate. Which I only managed to do by using a function. Is there a beter (faster) way to do it?
Code Snippet
SELECT OrderHeaders.ID, OrderHeaders.CurrencyCode, OrderHeaders.Period, dbo.GetExchangeRate(OrderHeaders.CurrencyCode, OrderHeaders.Period) AS ExchangeRate FROM OrderHeaders
FUNCTION GetExchangeRate
@CurrencyCode VarChar(3),
@Period int
BEGIN
DECLARE @VarResult Float
SET @VarResult =0
SELECT TOP 1 @VarResult=Rate FROM ExchangeRates WHERE CurrencyCode=@CurrencyCode AND ValidFromPeriod<=@Period ORDER BY ValidFromPeriod DESC
Return @VarResult
END
View 8 Replies
View Related
Aug 7, 2007
heyi'm having a problem with a stored procedure, to cut a long story short i need to replace apostrophe's in my text, like soSET @prOtherValue = REPLACE(@prOtherValue,''', '''')this doesn't work thowhats the work aroundcheers!!!!
View 6 Replies
View Related
Nov 2, 2005
does anyone know where i can find a user defined function that replaces accented characters with their normal ones
i want to replace all accented characters since for some reason sql server does not store the characters properly, or when they exported to excel for html they are displayed incorrectly
thanks
View 5 Replies
View Related
Feb 13, 2014
i have column in table which contains tabs and " i want replace with space...i am using repalce function is thier other way to improve performance with out using replace function.
View 9 Replies
View Related
Jul 20, 2005
Is it possible to use the REPLACE function in SQL Server 2000 so thatit returns a string containing only alpha-numeric characters (muchlike using regular expressions)?Thank you in advance for any suggestion.Darren.
View 1 Replies
View Related
Jul 1, 2015
Update query to modify a Url in the text column with another url
Ex:
Col1 Col2
1 An unexpected event occurred
https://abc.def.com/default/_workitem/10325
3 This alert occurs when service jobs run on
https://abc.def.com/default/_workitem/10118
10 This alert fired to indicated that error with
https://abc.def.com/default/_workitem/10150
to
Col1 Col2
1 An unexpected event occurred
https://abc.def.com/default/_workitem/11111
3 This alert occurs when service jobs run on
https://abc.def.com/default/_workitem/11111
10 This alert fired to indicated that error with
https://abc.def.com/default/_workitem/11111
View 3 Replies
View Related
Apr 17, 2014
I'm looking for a way to pull off a complex find+replace within some code, as follows:
@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'
We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...
Rules:
1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).
2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).
View 2 Replies
View Related
Jul 20, 2005
I have a table that has a Text datatype column that has gotten somegarbagecharacters in it somehow, probably from key entry. I need to removethe garbage, multiple occurances of char(15). The replace functiondoes not work on Text datatype. Any suggestions?
View 3 Replies
View Related
Jul 9, 2007
I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out?
Thanks,
Ridium
View 12 Replies
View Related
May 22, 2015
using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.
select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns
View 3 Replies
View Related
Feb 7, 2006
Dear Community,We have a problem with null-Bytes in varchar-Columns, which are nothandled correctly in our application. Therefor we try to filter themout using the Transact-SQL REPLACE function.The Problem was, that the REPLACE-function didn't behave the way weexpected.Following Example demonstrates the behavior:declare @txt varchar(512)declare @i intset @txt = 'hello ' + char(0) + 'world'print @txtset @i = 1while @i <= len(@txt)beginprint str(@i) + substring(@txt, @i, 1)set @i = @i + 1endprint 'Length: ' + str(len(@txt))print 'trying to replace null-byte:'print replace(@txt, char(0), '*')print 'replace Letter h'print replace(@txt, 'h', char(39))-- end exampleOutput:hello1h2e3l4l5o678w9o10r11l12dLength: 12trying to replace null-byte:*replace Letter h'elloThe Null-Byte replace destroys the whole string. This behavior occursonly on some of ourdatabases. The others work correctly.Is it possible that it depends on some server setting?ThanksEnno
View 5 Replies
View Related
Mar 22, 2007
Hello All.
Hopefully someone out there will have an idea as this isdriving me nuts.
Ihave some sample problem. I want to use function replace() on Derived Column.
For example.
when strDate = 2007/03/22
I used ==> replace(strDate, "/", "") ==> 20060322
But If strTest = "123.10" ====>> 123.10
How can i do to replace ( " )double qoute ?
by function replace()
what is a statement for replace (") in Derived Column ?
please tell me for this event.
any suggesstion appreciated
Thank you very much.
Chonnathan
View 11 Replies
View Related
Sep 4, 2015
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
View 4 Replies
View Related
May 14, 2008
Hi There,
Could someone please tell me why I am getting the above error on this code:
select (replace
(replace
(replace
(replace (serviceType, 'null', ' ')
, '<values><value>', ' ')
, '</value><value>', ',')
, '</value></values>', ' '))
from credit
serviceType (text,null)
Thanks,
Rhonda
View 1 Replies
View Related
Aug 20, 1999
Where can I find the list of new keywords for sql 7.0? Namely the keyword to allow duplicates when creating a table?
Thanks!
View 1 Replies
View Related
Sep 21, 2007
does anyone know whether the words 'comments' and 'narrative' have become reserved keywords post-SQL2000 ?
View 2 Replies
View Related
Apr 25, 2008
When designing a table, or column name. I am sure its best to avoid using table names or column names that use reserved words such as
"status" and "message"
What happens when we name a column or table using these words? Any tiny performance hit ? I am just wondering whats happening behind the scenes.
Thanks!
mike123
View 8 Replies
View Related
May 16, 2008
How can I find distinct ID'd in this table that has similar keywords to an ID I pass in like for example I pass in 1. It has 3 keywords. I would bring back 14 and 4 because 14 has bike and 4 has VAN
ID Keyword
1car
1boat
1van
14test
14car
14bike
4VAN
5 scooter
View 8 Replies
View Related
Dec 27, 2006
Hey
I got a SQL DB with three tables, where two of the collums of the main table gets its data from two other tables, ok? Now, on one page, called "search" the user should be able to search in the DB, and on another page the user should be able to register some keywords, my question is how can the user register several keywords in the same textbox, using perhaps a ; as a separator between the words? I hope you understand what I mean..
Thx!
View 2 Replies
View Related
Aug 24, 2004
I am using db keyowrds as attributes in my table such as from, to and date. These are enclosed in [] in SQL Server Enterprise Manager. I'm just asking if doing this is a bad idea? Reason being these are the most applicable names for these attributes but don't want to run into problems further down the line.
Thanks,
_z0
View 5 Replies
View Related
Apr 10, 2008
I WANT TO FIND THAT WORD WHICH IS START'S FROM % KEYWORD
SELECT ENAME FROM TABLENAME WHERE ENAME LIKE '%%'
HOW CAN I FIND PLS HELP ME OUT .
Yaman
View 3 Replies
View Related
