Transact SQL :: Modify URL With Replace Function

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


ADVERTISEMENT

Replace Function In SQL?

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

Using Replace Function

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

Using Like Function With Replace

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

REPLACE Function

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

Does T-sql Has Replace(in Vb) Function?

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

Using The REPLACE Function

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

I Have A Problem With The Replace Function.

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

Replace Function Not Working

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

Replace Function In Sql Statement

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

Problem With Replace Function

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

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 View Related

Replace A Function By Pure T-SQL

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

Make Sql Function To Modify Data Like Sql Procedure To Improve Code Structure And Readability.

Aug 27, 2007

Now Sql Function can not modify data as Sql Procedure like other database, it's very troublesome at most case!
in most case, Sql Function is used to improve code structure then it can be maintanced easy! BUT only because it can not modify data, there are 2 troublesome way:

1. Make all callers in the path from Sql Functions to Sql Procedure. and the coder will cry, the code will become very
confusional , and very difficult to maintance.

2. Divide the Sql Function into a thin CLR wrapper to call a Sql Procedure, can only use another connection, BUT can not be in the same transaction context And the code is ugly and slow.

You should not give limitation to Sql Function, should just limit the using context of Sql Function!

The sql code is more difficult to read and maintance than norm code(C#), then the improving code structure and readability should be one of your most important task, that's why microsoft!


View 6 Replies View Related

Replace Function: Replacing An Apostrophe With Whatever

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

Remove And Replace Accents Function

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

Way To Improve Performance Without Using Replace Function

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

Replace Function And Regular Expressions

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

T-SQL (SS2K8) :: Enhanced Find And Replace Function

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

Replace-type Function For Text Datatype

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

Replacing Multiple Strings Using The REPLACE Function

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

Transact SQL :: Parameter Replace And Add Quotes

Sep 25, 2015

Declare @tragetdb Varchar(max)
SET @tragetdb='xyz1'
 Declare @RestoreCmd Varchar(max)
SET @RestoreCmd= 'RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT='+''+ ' @targetdb'+ ''
 print @RestoreCmd

O/p:
RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT= @targetdb

But i am looking for 
RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT= 'xyz1'

 i tried using "" + "" , ' + ' , no luck..

View 5 Replies View Related

Transact SQL :: REPLACE Single Quote

Oct 24, 2008

I'm cleaning up a column in my table and getting rid of special characters.The only think I can't get rid of with the REPLACE function is single quotes.I'm doing aUPDATE TableSET Column = REPLACE(Column,'''','') --that's four single quotes then two single quotesBut the single quotes in my column wouldn't go away.I know that

DECLARE @string varchar(50) = 'test''s strings'SET @string = REPLACE(@string,'''','')SELECT @string

View 19 Replies View Related

SQL Server 2012 :: Select Query With REPLACE Function?

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

REPLACE Function Doesn't Work With Null-bytes

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

Sample Problem : Function Replace() In Derived Column

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

Transact SQL :: Replace Not Supporting Square Bracket?

Jul 1, 2015

update t1
     set t1.col1 = replace(t1.col1, t2.col1, t2.col2)
from table1 t1
inner join table2 t2 on t1.col3 = t2.col3

I am not able to replace some record which of t2.col1 contain like "[abc]def"

I have used "[][abc]def" and "/[abc/]def" for same but its not working.

View 4 Replies View Related

Transact SQL :: Replace A String Using Reference Table

Jul 21, 2015

I would like to create a function that will replace a string using a reference table

I have a table : reference

ID String ReplaceWith

1 ≈ &
2 < <
3 > >

If I pass a string into a function,  function needs to replace a string with replace with string column in reference table

For example, if I pass    a string : car $ap; fjld

The function should return car & fjld

How can i create a function like this so that i can call it in Stored procedure....

View 12 Replies View Related

Transact SQL :: Find / Replace Numbers In Server

Aug 12, 2015

I have a separate list of calendar years with radiocarbon year equivalents in SQL server but no conversion equation. Most but not all of the data I have is in radiocarbon years. I thought at first I could just link the tables but I don't want the data that is already in calendar years to be linked to this conversion table. Is there any way I can either link the two tables with criteria for which data is linked (Only ages that are in radiocarbon years). Or possibly a way to query all ages that are in radiocarbon years and do something similar to a find and replace with a large list of numbers to change?

View 14 Replies View Related

Transact SQL :: Replace Multiple Values Without Looping

Jul 13, 2015

I need one query:

create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>>  Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>>  Text Text Text <<Salary>>  Text Text Text')

[Code] ....

Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name

select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>>  Text Text Text <<Salary>>')

I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.

create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return

[Code] ....

View 5 Replies View Related

Transact SQL :: Replace Group By With CTE And / Or Cross Apply

Jul 10, 2015

If I Have a table like

Id(identity), PupilPersonId, EducationTypeId,VehicleTypeId,EducationDate, EducatorId,Canceled
661187       9242382         2                       1                2015-07-07 00:00:00.000 O_2 False
661183       9242382         2                       1            2015-07-08 00:00:00.000 O_2 False
661186       9242382         1                       1                2015-07-08 00:00:00.000 O_2 False
661178       9242382         2                       1                2015-07-10 00:00:00.000 O_2 False
661185       9242382         2                       1                2015-07-10 00:00:00.000 O_2 False

The result I want is the unique rows from columns:  

PupilPersonId, EducationTypeId,VehicleTypeId AND there MAX EducationDate
SELECT er1.* FROM EducationResult er1
INNER JOIN
(
SELECT
er.PupilPersonId, er.EducationTypeId, er.VehicleTypeId, MAX(er.EducationDate) as EducationDate

[Code] ....

I like to know is there another approach with CTE and or Cross Apply I can use instead?

View 5 Replies View Related

Transact SQL :: Replace With A Comma Separated List

Aug 5, 2015

I have a replace statement like the following:

select  (replace('FMG','FMG','FM'))

So this is straightforward as it will replace the word 'FMG' with 'FM'

However, SSRS is passing a comma separated list like this:

select  (replace('FMG','AFM','FMG','FM'))

And what I need to do is replace the comma separated list of 'FMG','AFM' only.  I tried this:

select  (replace('''FMG','AFM''','FMG','FM'))

But it still complaining about syntax errors. How do I get the comma separated list to be seen by the replace function?

View 7 Replies View Related

Transact SQL :: Using Regular Expression To Find And Replace Data

Sep 9, 2015

I am writing an SQL query to find an replace data in a column. I have a table that is filled with

C:usersXXXXappdata
C:usersYYYYappda
C:usersZZZZZZappdata

I would like to replace the c:usersXXXXX part with %userprofile%

The end result would be %userprofile%appdata

I know how to dot the replacement in powershell. it's quite easy

-replace "c:users[^]+","%userprofile%"  

Basically how would transfer this into SQL...

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved