Replacing A Portion Of Text String In Column
Jul 20, 2005
I need to replace a portion of a url in a column as a result of
changing servers. Is there a SELECT/REPLACE/UPDATE combination query
that can do this. The table has close to a thousand entries and would
be nice if a query can be set to do this. Tried the REPLACE example
in the BOOKS ONLINE but it creates syntax error, apparently because it
does not like the characters in the url and/or wildcards. I don't need
to replace the entire url, only the portion before ".com". Thanks in
anticipation of your help.
Pradip Sagdeo
View 3 Replies
ADVERTISEMENT
Oct 24, 2012
I have a column like this:
Table: User
name, field1
Jack 1000|1001|1003
Berg 2000|1001|2004
Paul 1001|1000
Jane 1001
Now, I would like to replace all "1001" with nothing, and also remove the "|"-separator behind 1001 if it exists, basically removing both "1001" and "1001|", so the resulting table looks like this:
name, field1
Jack 1000|1003
Berg 2000|2004
Paul 1000
Jane
My tries, been plenty, but here are some:
Code:
UPDATE UserTable
SET field1 = REPLACE(field1, '1001', '')
UPDATE UserTable
SET field1 = REPLACE(field1, '1001|', '')
But the above queries replaces field1 only if the whole field matches '1001' or '1001|'...
The above queries do work, just like I want them to. I just happened to write them in this order on this post... did not do a copy of the actual query.
View 1 Replies
View Related
Jul 20, 2005
Hi;I am trying to write a rountine ( below ) that will go into a colum oftext data type ( fae.pmcommnt ) locate the word "to" and replace it.I have the routine below. I get no error messages, but it also seemsto do nothing :).Any clues would be greatly appreciated.ThanksSteve================================================== =============declare @ptrP intSELECT @ptrP = PATINDEX('%to%', pmcommnt)from fae where projid ='00013'declare @ptrPC binary(16)select @ptrPC = TEXTPTR(pmcommnt)from faeif( TEXTVALID ('fae.pmcommnt', @ptrPC ) > 0 )print 'works'print @ptrPUPDATETEXT fae.pmcommnt @ptrPC @ptrP 2 'JJ'select projid, pmcommnt from fae
View 2 Replies
View Related
Jun 19, 2014
i need to cut my string on 3 portion, for exemple my string is :
a) 1.9999-Q1
b) 01.9999-Q11
I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:
a) 1
9999
Q1
b) 01
9999
Q11
View 9 Replies
View Related
Apr 22, 2008
Hello, this seems simple but I've been banging my head a while.
I have a data field that is formated like this: NYT/2000-Subways/7510-Electronics Mtc/7540-Data Svcs.
I need to pull out the string after the second / and before the third / in this case the value is 7510-Electronics Mtc but it does change.
I have this function that returns the first value (NYT):
USE [Data_Warehouse]
GO
/****** Object: UserDefinedFunction [dbo].[Get_Dept] Script Date: 04/22/2008 09:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Provide the phrase before (ex. 'Worklog Related to'), the field searched (ex. UPDATE_ACTION), and
-- the phrase after (ex. ']').
-- This function returns the string found after any phrase and before any phrase, within any field searched.
ALTER FUNCTION [dbo].[Get_Dept](@in_Phrase_Before varchar(250),@in_Search_Field varchar(250))
RETURNS varchar(250)
AS
BEGIN
Declare @str_String As varchar(250)
Declare @tmp_String as varchar(250)
Set @str_String = null -- to clear the variable buffer
Set @in_Phrase_Before=char(37)+(LTrim(Rtrim(@in_Phrase_Before)))+char(37) -- if 'Null', all strings will be returned
If Left(@in_Search_Field,4) = 'NYT/'
BEGIN
Set @tmp_String = Right(@in_Search_Field,(len(@in_Search_Field) - 4 ) )
Set @in_Search_Field = @tmp_String
END
If (PatIndex(@in_Phrase_Before, @in_Search_Field)) > 0 --Checks to see if the phrase before is in the search field
Set @str_String=SubString(@in_Search_Field,1, (PatIndex(@in_Phrase_Before, @in_Search_Field)-1) )
RETURN @str_String
END
Thanks in advance for the help
View 10 Replies
View Related
Jul 21, 2014
I want to grab some key value pairs from the text in sql column
e.g.
some text[Key1=Val1]some text[Key2=Val2][Key3=Val3]some text
I want a function which takes Keyname as input & returns the value related with it if found.
GetValueFmKey('Key1') should return Val1 and like on.
View 1 Replies
View Related
Jan 21, 2008
Hi,
I would like to replace all instances of a code that starts with 'GEM' with the word 'Perfect'', and all instances of the code that starts with 'GOLD' replaced with 'Imperfect'.
For example, if I have a table named STONES, and a field within the table STONES.Code contained codes such as 'GEMPART4000', 'GEMPART5000', 'GOLDSIDE2300' and 'GOLDSIDE3000', I want to return the first two codes as the words 'Perfect' for each and the last two codes with the word 'Imperfect' for each.
Similarly, how would it be written so I could select a middle part of the code to use as the trigger to replace the code with something different, e.g. if I wanted to use the 'PART' from the first two codes?
I have discovered that the REPLACE and CASE functions don't work with % (so I am looking for any code that starts with what I stipulate but can end with anything (or using it on either side of the middle part of the code)).
Any help would really be appreciated please.
View 5 Replies
View Related
Sep 18, 2007
I have a instance with many databases in it.
due to company/product name change,
I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures
and replace all of them with "abc" without affecting the databases an application.
Can u please help me with step by step guidance?
muralidaran r
View 14 Replies
View Related
Mar 20, 2006
Hi there,
I'm in a bit of a jam here and will appreciate any help.
I need the SQL code to replace a record if the record is empty.
For instance, I have about 7 columns containing over 40K records. In the firstname field, some records are blank. I need to replace all the blank firstname fields with this: 'now invalid' (without the quotes)
What would be the best way to achieve this?
Thanks
newbie:o
View 6 Replies
View Related
Jul 20, 2005
I have a large table, tblMessage, which stores e-mail messages in textfields. I need to remove the carriage returns the data in these fields,but I have not yet figured out how to do so.I thought that the way to do this would be with the REPLACE function;unfortunately, of course, the REPLACE function cannot work with TEXTfields. I tried CASTing the text field to VARCHAR(8000); however, someof the rows have more than 8000 characters in the text field, so it bombs.Here is the SQL that I tried:selectmsgID,msgSent,msgFromType,msgFromID,msgSubject,REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') ASnewMessage,msgOriginal,attIDinto tblMessageNewfrom tblMessageI'm at my wit's end. Truncating the text field to 8000 character is anacceptable option, but I can't even seem to be able to do that.I'm using SQL Server version 7.
View 2 Replies
View Related
Mar 25, 2008
Hi guys,
I have a large text that can have some ENTER character inside of it. The problem is that the text must be inserted into a txt file in a single row. So what I need is to find those ENTER character and replace them with a space so as to have the whole string in one row.
How can I build that formula in derivide column?
Thanks for your help
View 10 Replies
View Related
Mar 4, 2008
Hi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have:
- A Table called: Table_1
- A Field called: MyField
- MyField contains the value: Hello I am on the msdn forums
Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this?
Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums.
Appreciate the responses, Onam.
View 5 Replies
View Related
Dec 19, 2014
So I have the following column named String in a table:
<key>Children</key><integer>2</integer>
This of course can vary for the different records. What's the best way to replace the 2 with the contents of my variable with TSQL?
declare @children int
set @children = 4
I want to do something like this:
SELECT <key>Children</key><integer>@children</integer>
View 4 Replies
View Related
Oct 7, 2015
I'm trying to replace a particular part of a row in a table with a new value.
The row is called "DataPath" and it has a lot of values like so:
mashOperationsComponent Data FilesSantec
I want to run a query to replace the mash with our DFS namespace share name companysharesDepartments but keep everything else past the mash part of the original row.
I'm currently running this query, it says it is altering 30,000 rows, but it doesn't look like it's doing anything at all..
UPDATE dbo.Part
SET DataPath = REPLACE(DataPath,'company.localsharesDepartments','mash')
WHERE DataPath like 'mash\%'
So for example, it would change the mash above to
company.localsharesDepartmentsOperationsComponent Data FilesSantec
View 2 Replies
View Related
Jan 20, 2014
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
View 9 Replies
View Related
Nov 20, 2013
I want to replace the value in a column with the content from listbox.
I have wrote the code like,
Dim i as Integer
dim sql as string
For i = 0 to Customerslist1.ListCount-1
sql = "UPDATE master (gstl) VALUES(" +chrb(34)+Customerslist1.Cell(i,8)+chrb(34)+")"
app.Gudangstock.SQLExecute(sql)
app.Gudangstock.commit
next
msgbox"insert ok"
UpdateCustomerList1
but no error found and the sql command is not executing.
View 5 Replies
View Related
Dec 3, 2007
Hi Guys,
I am faced with a problem that is giving me headaches. I have t1, t2 and t3. I t1 I have students that have a reference to t2 which are the schools they belong to. The problem arises when I see that there is redundancy in t2. There more records for the same schools. This was posible in giving the same school (postcode and name make it the same school) a different ID. In t3 I removed these redundant schools by using fuzzy grouping.
My problem is I want to ensure that the students are put using the schools from t3 instead of t2. So what I need to do is is to replace the redundant id from t2 with the correct id from t3.
Please help. Thans in advance
View 3 Replies
View Related
Dec 24, 2005
In an *existing* database, how would you remove the City column in the
Employees table, and put in the CityID key column from the Cities table
?
View 2 Replies
View Related
Dec 2, 2013
I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like
UPDATE Coupins
SET Name='test1,test2,test3'
WHERE Name='test4,test5,test6';
I need basically to swap the Name value of these without affecting any other values.
View 4 Replies
View Related
May 17, 2006
Hello. We have a list of 1000+ sku numbers. 90% of them start with u. I need to replace the u with T.
Concerns:
It has to only replace the first letter, if there are other "u"s in the sku, they need to remain.
Not all skus start with u, those that don't should remain the same.
I have searched and searched because I'm sure someone has requested the exact same thing but I can't find anything that is identical.
I have this so far but I know its not correct:
set sku = replace(left(sku,1),'u',right(rtrim(sku),1)+
'T')
Can someone help lead me down the right path?
Thanks in advance!!!
View 4 Replies
View Related
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
View 3 Replies
View Related
Oct 9, 2006
hi.
I have to update a column with new data. I have to replace the values that lay with a certain range. for example
my table has a column called ENI_KEYVALUE1, I have to update the data in this column only where the ENI_KEYVALUE1 is between P6_00001 and P6_00240. The values have to be replaced with values listed sequencially in an excel spreadsheet.
how do I do this???
thanks in advance
View 2 Replies
View Related
Apr 27, 2006
Hi there,
I have a derived column shape in which I replace a column with an expression.
The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)
Like this.
ColumnName DerivedColumn Expression
ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA
But whenever, the condition is NOT met, ColumnA is set to NULL!!!!
Does this mean that the column value is deleted before the Expression is applied?
If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset??
Cheers.
View 13 Replies
View Related
Nov 13, 2001
Hi,
this is my second attempt to get an answer to this question.
We want to strip our firstName column and lastname column of any punctuation that might be present.
What's the best of doing that?
Is my only choice to write a nested REPLACE for each character we want replaced (which will end up being very very long) or is there another way.
thanks
Zoey
View 2 Replies
View Related
Nov 4, 2015
I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.
Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.
View 3 Replies
View Related
Aug 27, 2015
I have a parameter value as shown below and this is dynamic and can grow
Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL --- each state to be followed with comma and the next state
correct query that will fetch only state names and not the numbers.
View 8 Replies
View Related
Jul 14, 2015
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
View 5 Replies
View Related
Jun 25, 2007
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
View 7 Replies
View Related
Mar 5, 2007
Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
View 7 Replies
View Related
Jul 20, 2005
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies
View Related
Oct 31, 2002
Hi,
I have a table with col_noteText defined as 'Text' datatype column.
I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText.
Does anyone know how to do this for 'Text' datatype column.
Thanks in advance.
jfk
View 1 Replies
View Related
Feb 19, 2004
Hi SQL Gurus,
I have an XML question to which I am kind of new.
I have an XML string that I get by using a query similar to the following....
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Zip_ROOT!1!],
NULL AS [Zip!2!Value!element],
NULL AS [Zip!2!State!element]
FROM ZipCodes
UNION
SELECT 2,
1,
NULL,
Zip,
State
FROM ZipCodes where id < 3
FOR XML EXPLICIT
This returns an XML string, which is very long. I need to take this string and INSERT/UPDATE to a Text column in a table.
Any kind of help in this regard will be grately appreciated.
Regards,
Varma
View 4 Replies
View Related
Sep 18, 2007
I am not very familiar with MS SQL, and I need to know how to search a table for a string. I know the column that it is in, but I don't know which record. I am looking for a small piece of text in a much larger text field (we are talkinga maybe a 10k char field and I am only looking for about a 15 char string).
How do I do this with a MSSQL query?
Thanks a bunch
Nevermind, i found PATINDEX(). That worked great.
View 1 Replies
View Related
