I have to write a query to update the table to remove dots .... from the string value.
so i could write.
UPDATE tbaddress
SET Title='New address'
WHERE Title='New address....'
but, there could be more than this specific record in the table, so i have to find each single record to do this. Is there a way to update the table so I can just remove the .... but rest of the text remain.
After parsing unformatted XML file, we are loading XML in formatted for into a SQL table rows, so that SSIS can read it and load it to DW tables.
We have a flag column in the above table, which gets updated after each row is extracted successfully by the Procedure(cursor inside Proc) used in SSIS, but cursor inside Procedure is taking 16 hours to load 100k xml source files, if we remove cursor and use bulk load then it takes only 1.5 Hrs. but with bulk load we cannot update the flags.
We've installed the Oracle provider for OLE DB on SQL Server 2005, which has the default collation (SQL_Latin1_General_CP1_CI_AS), and we've created a linked server for the Oracle 9.2.0.5 database, which has AL32UTF8 as the database character set. We can successfully insert strings into VARCHAR2 columns on Oracle from SQL Server via EXEC SP_EXECUTESQL('INSERT OPENQUERY(...) VALUES(...)') -- as long as the strings (whether selected from NVARCHAR columns on SQL Server or specified as literals with the N prefix during testing) only contain Windows-1252 characters.
If the SQL statement contains a character above U+00FF, the string on the Oracle side is incorrectly/doubly encoded; there are nearly (but not exactly) 4 bytes per character instead of the 1 or 2 you'd expect from ASCII/Latin-1 characters encoded as UTF-8.
We've tried reconfiguring the linked server: collation compatible = false, use remote collation = true, and collation name = Latin1_General_BIN2. But that had no effect.
I was wondering if someone could tell me how to use an SQL Function to remove everything from the mess of characters below except 'Test.' Really, any message could be substituted, so it's not as easy to leave everything except the 'Test.' string.
{ tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fnilfcharset0 MS Shell Dlg 2;}{f1fnil MS Shell Dlg 2;}} {colortbl ; ed0green0lue0;} {*generator Msftedit 5.41.15.1507;}viewkind4uc1pard x720cf1f0fs20 Test.f1par }
It's RichText.
The fs20 could be fs24, fs25, etc. (any numbers). The end of the number would be the point to trim the beginning off.
The '' seems like it would be the point to start trimming the end off.
I've been experimenting with SUBSTRING together with CHARINDEX, but am just not getting even close to coming up with a solution.
I need retrieve only strings that hold the sub-string in middle. no first no last just middle.
See: the sub-string is "test"
Yes: test test test Yes: take test test Yes: test test take Yes: take test take No: test test No: test take take No: take take test No: test
I prepare sample data...
declare @sample table(s varchar(50)); insert @sample values ('test test test'), ('test test'), ('test'), ('test take test'), ('test take take'), ('take test test'), ('take test take');
I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.
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.
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex: String value is 10 characters long. The string value is X000001250. The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
I am using SSIS 2012 SP1 to import a comma delimited csv file into a SQL table.
One of the fields carries a time value:
Source = textfile, column=DT_STR(8), value format = "hhmmss", e.g. "011525" Destination = field in SQL table, data type = time(0)
To get it from the textfile to the SQL table I am:
1.) Creating a derived column called [d_Time of Entry]with the following formula -
SUBSTRING([Time of Entry],1,2) + ":" + SUBSTRING([Time of Entry],3,2) + ":" + SUBSTRING([Time of Entry],5,2)
2.) Performing a data conversion task to convert [d_Time of Entry] from DT_STR(8) to time(0) The upload fails because values that start with a zero, i.e. times before 10am, have their leading 0's stripped before being derived. You can see this because "011525" is derived as "11:52:5" when it should be "01:15:25".
I am using the following query to calculate date differences:select ..........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.
I have a table that holds a large amount of text in a field that is the body of the email. For example, it might say something like:
Quote: Email tech support at thisemail@email.com if you have any questions about the results of this test.
I need to change the email address in this field. Using this example I need to change thisemail@email.com to thatemail@email.com; however I do not want to change the other text in that field.
It is also important to note that the rest of the body of the emails stored here is different depending on the email.
So basically what I need is a statement that would look at a particular field, search for an email address, and replace that email address with another one without disturbing the rest of the text in that field. I already checked the w3 update tutorial and the update there is for the entire field.
I wrote this query to pull some information involving 3 tables dbo.NewFamNbrs$, dbo.CGIItemMaster and dbo.CGIFamilyMaster. In addition I want to perform an update statement to update column dbo.CGIItemMaster.FamilyIDX with the information of dbo.CGIFamilyMaster.IDX. I also know that truncation is indicating that I'm trying to insert data into a field not large enough. So I performed a len() function on the both columns and order by desc and they both came up with 4 as being the max.
Query:
SELECT a.PART, a.Family, b.FamilyIDX, c.FamilyID, c.IDX FROM dbo.NewFamNbrs$ a LEFT OUTER JOIN dbo.CGIFamilyMaster c ON a.Family = c.FamilyID LEFT OUTER JOIN dbo.CGIItemMaster b ON a.PART = b.PART
Results of query:
PART Family FamilyIDX FamilyID IDX 000127233TF011468 TF01 506 000129880TF011468 TF01 506 003110 MET061468 MET06 1915
UPDATE STATEMENT:
begin tran update dbo.CGIItemMaster set FamilyIDX=c.IDX FROM dbo.NewFamNbrs$ a LEFT OUTER JOIN CGIFamilyMaster c ON a.Family = c.FamilyID LEFT OUTER JOIN dbo.CGIItemMaster b ON a.PART = b.PART
Hi, What is the difference updating a null value to char/varchar type column
versus empty string to char/varchar type column?Which is the best to do and why? Could anyone explain about this?
Example:
Table 1 : tCountry - Name varchar(80) nullable Table 2 :tState - Name char(2) nullable Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state So,when the state is not present for the country ,i have two options may be - null,'' tCountryDetails.State = '' or tCountryDetails.State = null?
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description 1234 1 Talk to Mr. Cartwright about 1234 2 new issues with patent law. Conferece 1234 3 call to discuss payment of past bills.
I need to see 1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
Is it possible to put in a string value as one of the results? I'm trying to produce a string in the data table is the value is null so I want to do something like:
Hi! I am trying to remove an "*" I used to transfer a file from Excel to SQL. I put the "*" in the NULL values so that I would not get an error. Now that I have the file in SQL, I need to remove the :*" from some columns in my table. How can I select all of those in a column and delete them without deleting each individual one. Any ideas?
I am trying to develop a web site. I have a local ms sql database on my machine. I am trying to connect to a ms Sql database on a goDaddy server from the application. I am trying to understand the connection string and its total properties. here is what I think should be in my web.config file < add name="Personal" connectionString="Server=whsql-v12.prod.mesa1.secureserver.net; Database=DB_XX10; User ID=myID; Password=myypassword; Trusted_Connection=False" providerName="System.Data.SqlClient"
/> <remove name="LocalSqlServer"/> can someone please tell me where I am going wrong, Thanks for your help.....
I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>" SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)"> <SelectParameters> <asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string Dim y as string = "'" // " ' " key = Request.QueryString("key").trim(y.tochararray) But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up? string y = "'"; key = Request.QueryString["key"].trim(y.tochararray());
How can i make a sum (concatenation) of strings of one column in a table. for example i have a table like this field1 field2 1 abc 1 bcd 2 sdf 2 sdd I want to get these strings added group by field 1 Thanks
I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table. so like InsertCommand="INSERT INTO customer_mod (phone) Values (@Telephone_1) <asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" /> I don't think that is gonna work, so can you please help me make that code work?
I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work. I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name. My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation. I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what?????