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


ADVERTISEMENT

Transact SQL :: REPLACE Part Of String With Different Piece Of Text

Apr 20, 2015

I have a string column in a DB where it's values contain the following midway through the string ([DOCUMENTGUID] is a uniqueidentifier that is different for each row):

<a href="../downloadDoc.aspx?dg=[DOCUMENTGUID]" target="_blank">

I would like to replace this part of the string with a different piece of text.

I know that I should be using PATINDEX and REPLACE functions but I don't know how to utilise.

View 4 Replies View Related

Transact SQL :: Find Circular Reference In The Table

Jun 1, 2015

Create table #tblActvity
(
activityIDvarchar (50),
activityParentIDvarchar(50)
)
Insert into #tblActvity
SElect '1',Null

[Code] ...

--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....

I need a sql qry which will require to find a circular reference in it.....

As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6  or 7 it should return 1 as they have circular reference....

View 4 Replies View Related

Transact SQL :: Database Constraint Check On Table With Reference Key

Jun 11, 2015

CREATE TABLE PRODUCT_SALES
(
Salesmanid BIGINT,
Productid BIGINT
)

INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)

SELECT * FROM PRODUCT_SALES

/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter

INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),

It should throw error , if possible user defined message would be more useful.

View 7 Replies View Related

Replace String In A Table - Update Column Using Variables

Jul 3, 2014

Trying to replace a string in a table and it is not working the path can be like OM-WD08-1 reportData.raw

USE Config

DECLARE @OldPath varchar(30), @NewPath varchar(30)

-- Initialize the variable
SET @OldPath ='OM-WD08-1';
SET @NewPath ='AA-PC';

UPDATE AnatomyConfigs
SET Path = REPLACE(Path,@OldPath,@NewPath) WHERE Path IS NOT NULL
AND Path LIKE @OldPath
GO

View 3 Replies View Related

Transact SQL :: Find All Stored Procedures That Reference Oracle Table Name Within Server OPENQUERY Statement

Aug 10, 2015

One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?

View 2 Replies View Related

SQL Server 2014 :: Find String With Spaces And Replace Them With Other String

Sep 8, 2015

I have following query which return me SP/Views and Functions script using:

select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function

I need its result as:

Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function

I used following

select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?

View 5 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

Transact SQL :: Search For String In Table

Apr 29, 2015

Currently we have requirement like below.

Input @Filename ="233-sssee-FILENAMETYPE@ssss.xml"

In the DB i have column values like below.

FileType              Val
FILENAMETYPE      Direct

Now my requirement is to search for FIleTYPE in above table by passing  @Filename  as parameter and that should return Val as response. How to write a search query for this type.

View 10 Replies View Related

Transact SQL :: Split String As Columns And Insert Into Table

Aug 20, 2015

I have a string ,want to split the values after every space as column value and insert them into a table 

 1306453 0 0 0 0 0

col1      col2  col3 col4  col5 col6
1306453    0         0       0         0       0

View 7 Replies View Related

Transact SQL :: Find Table And Column Names From String Data

May 29, 2015

I have a SQL text column from SP_who2 in table #SqlStatement:

like 1row shown  below :

 "update Panel  set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"

I want to find what table and column  names are in the text ..

I tried like below ..  

Select B.Statement from #sp_who2 A  
LEFT JOIN #SqlStatement B ON A.spid = B.spid 
 where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C 
ON T.object_id=C.object_id
WHERE T.type='U'


Something like this : find the column names and tables name

View 18 Replies View Related

SQL To Replace String

Jul 26, 2002

Hi,
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

View 3 Replies View Related

SQL To Replace String

Jul 26, 2002

Hi,
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

View 1 Replies View Related

SQL To Replace String

Jul 26, 2002

Hi,
I need to write a replace sql in TSQL 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

View 3 Replies View Related

String Replace

May 7, 2008

I've got a simple table with a column called html. In the html column I need to replace all occurances of <BR> with <br />

Can I do this with an update?

Thanks

View 1 Replies View Related

How To Replace String

Jun 3, 2014

if I have this string "ABCD test love", is there a way I can get rid of all text after "test" so that it becomes "ABCD test".

View 2 Replies View Related

How Do I Reference A Connection String In C#?

Apr 18, 2008

 SqlDataAdapter da = new SqlDataAdapter(sql, "Data Source=xxxx;Initial Catalog=xxxx;Integrated Security=True"); I want to use my ConnectionString variable but I can't figure out how to put it in there without the compiler throwing an error.: ConnectionString="<%$ ConnectionStrings:UMBPRMConnectionString %>   

View 1 Replies View Related

Can I Convert/reference A String To A Var?

Jul 13, 2006

hey, guys

I got a strange question. :)

In SQL Server,
declare @Item8 as varchar(100)
declare @str as varchar(100)
set @str = '@Item8'
Is there any way I can reference @str to @Item8 ?

Thanks

View 1 Replies View Related

Transact SQL :: Cross Reference 2 Tables

Jul 28, 2015

I have 2 tables, OBJECTS and LINKS both have common field(OBJECT_ID).

I need to update certain records in table OBJECTS only if they meet certain criteria in table LINKS.

How do I go about doing this..???

View 8 Replies View Related

Replace Char At End Of String

Mar 2, 2000

Hi,

I have a table where this one column(varchar) has rows of data with a period at the end of the string. Is there any function I could use to remove all the periods?

For eg:
bhjio.
shtpl.

should become:
bhjio
shtpl

Thanks

View 1 Replies View Related

MS SQL 2000 String.replace

Jul 12, 2007

Hello

with the following query :

SELECT words FROM T1

i get :

A,B,C

how can i get

A > B > C

something like String.Replace(words , ',' , ' > ')

thank you

View 3 Replies View Related

Update String/replace?

Jan 15, 2008

Hi experts,

I need to do an update on a field which will update 3 characters within a string. Right now I have '000111000333'. I want to replace '000' starting from the 7th character space with '222'. The first set of zeroes will remain unchange. I want the final outcome to be '000111222333'. How can I do this?

I have tried searching this forum and could not find anything. the closest thing I can find is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81890#360936.

Thanks for the help!

View 2 Replies View Related

Analysis :: Replace String In Mdx?

Jun 25, 2015

I have employee members like

"firstname , secondname" in my dimension

i want remove , and display it like "firstname  secondname"

i can do the same in sql server but i have to do it in mdx

View 4 Replies View Related

Replace A String Column With 'Y' Or 'N'

Nov 12, 2007

Hi,

I am trying to replace a string column with 'Y' or 'N' in my SSRS report. I tried the following expressions, but no one works for me.


=IIf(Fields!ExpectedWords.Value Is Null, 'Y', 'N')

=IIf(Fields!ExpectedWords.Value = '', 'Y', 'N')
=IIf(IsDBNull(Fields!ExpectedWords.Value), 'Y', 'N')


Would you please tell me what's wrong? Your suggestions would be appreciated.

Thanks alot.

View 3 Replies View Related

Transact SQL :: Replace Column Value From ASCII Characters To Non ASCII Characters In Table?

Oct 22, 2015

I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.

Note – values in column must be same

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

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

Advanced Transact-SQL Reference Book - Need Recommendations!

Jul 20, 2005

I currently have two SQL server books for MS SQL Server 2000. One is aprep book for the 70-229 exam, the other is a Wrox book: "professionalSQL Server 2000 Programming."I'm looking for more T-SQL books that give me PRACTICAL tips onwriting advanced queries. What book do you refer to? Please post them.

View 3 Replies View Related

Transact SQL :: Server Reference Field From Adjacent CTE

Oct 22, 2015

I am trying to eliminate a scalar function by rewriting a view. I currently have a structure similar to the following:

With FirstQuery as (SELECT Trial, SBOI, TBOI,
FROM TU
WHERE SBOI Between 22000 and 22999 and TBOI < 10000),
strt as (SELECT TOP 1 TimeOfEvent, Trial, SBOI, TBOI
From TU ORDER BY TimeOfEvent),

[Code] ....

What I trying to do is for the Trial, SBOI, and TBOI in FirstQuery, find the Start Time and Stop Time. TimeOfEvent is an integer in milliseconds. Say I have the following data:

TrialID     SBOI     TBOI     TimeOfEvent
A             22000   5000     5
A             22000   5000     10
B             22000   5000     8
B             22000   5000     15

So the DISTINCT output would be:

     Trial     SBOI     TBOI     StartTime     StopTime
     A         22000   5000     5                  10
     B         22000   5000     8                  15

The problem I am having is that strt is selecting the smallest time period. It doesn't seem to care what the current record in cte is. I thought that by using my joins, it would make it select smallest TimeOfEvent for the Trial, SBOI, and TBOI that are selected in cte. Obviously that is not the case. So, I was trying to add a WHERE to the strt Select Statement such as 

WHERE TrialID = FirstQuery.TrialID and SBOI = FirstQuery.SBOI and TBOI = FirstQuery.TBOI but it isn't working either.

What can I do?

View 5 Replies View Related

How To Replace Integer With A String In A Query

Apr 15, 2008

Hi all, In a sql query I need to replace 0 (zero) with "Not rated" ...Can some one help me to do this.In short: how to replace a integer value to a string in a query? is it possible?Thanks for the HelpRamesh 

View 2 Replies View Related

Find And Replace String Manipulation Help

Mar 14, 2008

Please help me with the sql script to manipulate the string data:

I need to add <Text> at the beginning and end of the string.
I also need to add <option> before the first and after last occurence of the <Option> string. The original string

<StockNumber>502</StockNumber>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Warranty>1</Warranty>

should look like

<Text>
<StockNumber>502</StockNumber>
<Option>
<OptionKey>113</OptionKey>
<OptionKey>151</OptionKey>
<Option>
<Warranty>1</Warranty>
<Text>


Thanks.

View 6 Replies View Related

Results To String Array For Reference Within CLR Proc

Jun 13, 2006

I keep running into a problem with the debugger freezing up. I'm trying to pull results from a query into an array (not more than 10 rows)

cmd.ExecuteReader();

while (rdr.Read() == true) {

letter = rdr[0].ToString();

i += 1;

}



if I comment out the "letter = rdr[0].ToString();" portion of code, the project runs fine. With that code there, I can not step into the proc from the SQL test script. No errors are raised. VS just freezes.

View 1 Replies View Related







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