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


ADVERTISEMENT

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

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

Derived Column Logic To Replace Empty String With Null?

Nov 7, 2006

Ok.. so I have a fixed position data feed. I read the file in as just whole rows initially, process a specific position and evaluate a conditional split to determine direction of the file for proper processing (file contains multiple recors with different layouts). This all works fine. I then use the derived column feature to process all the columns.

Most of the columns are as simple as SUBSTRING(RecordData,1,10) for example to get the first column. This all works for string data. I then have a column that is a date field. The problem occurs that the code SUBSTRING(RecordData,20,10) returns either a date or empty set of data if no date was in the original file. When this gets sent to the OLEDB connection (SQL Server 2005) into the date field it fails. If the record has a date it works, but if it is empty it fails the insert.

I tried to replace empty strings with NULLs with this code. REPLACE(TRIM(SUBSTRING(RecordData,20,10)),"",NULL(DT_WSTR,10)). This does not work. So my question is how do I bring a date field from a fixed flat file into a SQL datetime field using a derived column? More specifically is how do I set it to NULL if its empty data? When I use the above code it inserts all the rows from the file, but it sets all rows to NULL not just the empty ones.

Thanks.

View 6 Replies View Related

How Do I Handle REPLACE A Particuler Matched String Within An NTEXT Column In SQL Server?

Mar 27, 2008

Hi!
I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error

I tried following

UPDATE CaseTypeDefs SET definition = replace(LTRIM(RTRIM(definition)), '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]>', '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]>')


But this is producing following error

Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.

For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.

Need help, how to do it?.


Thanks In Advance

Devloper
Anil Kumar Dwivedi

View 4 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

We have the following two tables :

Link  ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

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

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

Update One Table When Records Inserted In Another Table - Variables In Trigger

May 19, 2014

I am trying to update one table when records are inserted in another table.

I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.

My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:

ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)

[Code] ....

View 4 Replies View Related

Replace Data In SQL Server Table Column

Apr 14, 2004

What is the correct syntax to replace a field data nvarchar(50)

Current data = 0020-10-02
Change = 2003-10-02

Thank you in advance.

View 8 Replies View Related

Table Variables And Update Statements

Jul 20, 2005

HiI'm using the SQL 2000 table variable to hold 2 different fact sets.I'm declaring the variable @CurrentTable and inserting into it using aSELECT statement with no problems.I'm leaving certain of the columns null in order to later update themwith the PK.Problem is in the UPDATE syntax I'm usingUPDATE @CurrentTableSET ManagerTitle = (select mgrs.pos_title from mgrs) wheremgrs.pos_num = @CurrentTable.MgrPosNumIt is insisting I declare the @CurrentTable variable when I try to useit in the where clause.Is it simply out-of-scope or am I really doing something foolish?Andrew

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

Table And Column Request As Variables Error.

Jul 20, 2005

I am trying to run a query where my Table and my request are variables(Will be used in Stored Procedure)Declare @EmailVARCHAR(100)Declare @TableVARCHAR(50)Declare @Count VARCHAR(8)DECLARE @cmd VARCHAR(500)set @Table = 'tblManager'set @Email = 'cauer@tampabay.rr.com'Set @cmd = 'Select count(*) as Count from ' + @Table + ' WHERE Email= ' + @Emailexec(@cmd)The error I get isServer: Msg 207, Level 16, State 3, Line 7Invalid column name 'cauer@tampabay.rr.com'.I have worked on this for a while, any help would be greatlyappreciated.Chris Auer

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

Update One Colum With Other Column Value In Same Table Using Update Table Statement

Jun 14, 2007

Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani

View 3 Replies View Related

Variables As Table Or Column Names In A Stored Procedure

Apr 16, 2008

I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours
@DayName varchar,@DayIDName varchar
AS
BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName
INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName
INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID
END
RETURN
...is this possible?? if so how?
Thanks

View 2 Replies View Related

Transact SQL :: How To Insert Dynamic Column Values Of A Table To Variables

Jul 18, 2015

I am trying to insert different number of columns into variables.  This is what it does If I use a static columns.

declare @AccountType nvarchar(10)
declare @Total numerical(15,2)
declare @1 numerical (15,2)
declare @2 numerical (15,2)
declare @3 numerical (15,2)

#MonthtoDate  temp table is created using a dynamic pivot query. 

Data looks like this :

Account Type  1 2
3 Total
Type 1 3
0 4 7
Type 2 5
7 1 13

Select @AccountType = AcctType , @Total = MonthToDate, @1 = [1], @2 = [2], @3 = [3]  from #MonthtoDate 

However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .

We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .

View 6 Replies View Related

SQL Server 2012 :: Update Table From Variable Table Column?

Oct 6, 2014

I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.

This is my statement:

UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)

View 2 Replies View Related

Find String In Any Column In Any Table

Sep 13, 2001

quote:Another question is how to find a string in any column of any table.
(Above is a hint as to one way)
I had to do this because a system was sending out a not very impressive email to users ad we couldn't find what was triggering it.


I have run into a similar situation. Care to share your solution?

Cat

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

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

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

Pass NVARCHAR (or Other String-type) Var As A Column Or Table Name In SQL

Jun 21, 2004

Hey folks, the question is fairly simple, unfortunately the answer has proven rather elusive.

Is it possible to declare a variable which would then be used to identify either a column or table in an SQL statement?

Here's a basic idea of what I'd like to do:

DECLARE @myVar AS NVARCHAR(50)

SELECT *
FROM @myVar

or

DECLARE @myVar AS NVARCHAR(50)

SELECT @myVar
FROM MyTable



I'm probably looking for some sort of built in function that will accept an argument here... like COLUMN(@myVar) or something of the like. I just don't know where to look...

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

Update One Table With Value Of A Column In Another Table Plus A Constant

Sep 19, 2012

Need to update one table with value of a column in another table plus a constant:

UPDATE TABLE_A
SET TABLE_A.COLA=TABLE_B.COLB+'10'
FROM TABLE_A
INNER JOIN TABLE_B
ON TABLE_A.COLA=TABLE_B.COLA
WHERE TABLE_A.COLA=TABLE_B.COLA

The above statement works except the concatenated string has space between TABLE_B.COLB text and '10'. How to remove the space (4 characters) so that the string shows as one word 'TABLE_B.COLB10'?

View 2 Replies View Related

T-SQL (SS2K8) :: Update One Column For One Table From Another Table?

Jul 8, 2014

I have to tables say 'employee1' and 'employee2'

employee1 has lastname
employee2 has firstname, lastname

i have to update firstname in employee1 from firstname in employee2 table and the common field for both tables is 'lastname'

View 8 Replies View Related

Update Table Column Based On Value From Another Table?

Sep 2, 2005

Hi, I have two tables. I want to update two columns in my first table,[ADD_BSL_SALES] and [ADD_BSL_COST] with two values [Sales] and[Costs] held in my #temp table but based on a RUN_DATE from my firsttable.Can anyone point me in the right direction?Thanks in Advance ď?ŠBryanCREATE TABLE [GROSMARG_AUDIT_ADDITION] ([RUN_DATE] [datetime] NULL ,[SALES_DIFF] [numeric](19, 6) NULL ,[COST_DIFF] [numeric](19, 6) NULL ,[ADD_BSL_SALES] [numeric](18, 0) NULL ,[ADD_BSL_COST] [numeric](18, 0) NULL ,[ADD_SALES_DIFF] [numeric](18, 0) NULL ,[ADD_COST_DIFF] [numeric](18, 0) NULL) ON [PRIMARY]GOINSERT RUN_DATE,datetime,INSERT SALES_DIFF,numeric(19,6),INSERT COST_DIFF,numeric(19,6)INSERT ADD_BSL_SALES,numeric(18,0),INSERT ADD_BSL_COST,numeric(18,0),INSERT ADD_SALES_DIFF,numeric(18,0)INSERT ADD_COST_DIFF,numeric(18,0)--- Second TableCREATE TABLE #DUPTOTALS[Sales][Costs]

View 1 Replies View Related







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