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')
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!
I am using an OLEDB source to run a stored procedure, which returns records from a temp table. The destination table is exactly same as the temp table in the stored procedure. I've some collation settings on both the destination table and temp table, but both are exactly same.
I got Unicode to Non-unicode conversion error first. I dont know why it happened as there is no difference in source and destination table. I solved that issue using the data conversion component. Then I got the truncation error. I set the Ignore on Truncation on error output to get rid of that issue. Then the package executed without any problem. But all the nvarchar and varchar fields in the destination table got populated with only the first letter of data.
I have a large website with over 100,000 images and the location of the images are stored in a column (img_url) as below:
/images/imagename.jpg
Because all these images are stored in the same folder it is hard to manage so we want to store each image under a directory based on the 1st letter of the image name, ie:
/images/a/aimage.jpg /images/b/bimage.jpg
I can automate the physical move of the images into the correct directory but I need SQL update query that will update each column based on the 1st letter of the image.
Hi All,I have this data file with fix length(see below). I am able to insertit into the database using bcp, but now I want to skip (do not insert)the row which start with letter 'S' into the database. Is there away todo it? By the way I am using -F2 option to skip the first record.Here is my data:Record 1 04XXX2 13106900240120042003040045061 Testing N POLYDOROS TRUSTEEE2 12621241640280041004040045633 What are they MARTIN &XXXXXS C1000003200400409850000059611000000500001000000001 9613000000576497500S X1000003200000209850000059613000000000000000000001 9613000000573497000Thanks for your help.Ted Lee
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.
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.
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.
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.Â
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.
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)
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
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.
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.
I need to replace a portion of a url in a column as a result ofchanging servers. Is there a SELECT/REPLACE/UPDATE combination querythat can do this. The table has close to a thousand entries and wouldbe nice if a query can be set to do this. Tried the REPLACE examplein the BOOKS ONLINE but it creates syntax error, apparently because itdoes not like the characters in the url and/or wildcards. I don't needto replace the entire url, only the portion before ".com". Thanks inanticipation of your help.Pradip Sagdeo
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
--------------------------- Glossary --------------------------- Term | Definition ---------------------------
What I need to do is find all the letters that are at the beginning of the terms.
So if I have "apple" as a term, A is returned. Also, if I have "forest", "fruit", and "flower", F is returned, but only once. And if I have no terms that start with Q, Q is not returned. Lastly, if I have a term like " 'Walking' Pneumonia", it should recognize that the term starts with a W.
Hello!I want to get the first letter in a sql-query and compare with a QueryString.This is the pricip in ASP Classic:Select * From Dictionary Where Left(_Name, 1) = Request.Querystring["Letter"]I think you'll understand.Thanks.
In my drop down menus - which are populated by tables - we can only use the scroll bar OR type in the first letter to find the item - Is there a way to type in several letters to find something? (e.g. if I want to find clockwork - right now I could type in "C" to get to the C's - but if I tried typing "clo" it would end up at the beginning of the "O's" - Thanks
SELECT name = SUBSTRING(name, 1, 1), total = COUNT(SUBSTRING(name, 1, 1)) FROM products GROUP BY SUBSTRING(name, 1, 1)
the assignment said that,
quote:"Do not display the letter and count unless at least three product names begin with the letter."
but i got the errors when I try following.
WHERE total >= 3
quote:Msg 207, Level 16, State 1, Line 4 Invalid column name 'total'.
WHERE COUNT(SUBSTRING(name, 1, 1)) >= 3
quote:Msg 147, Level 15, State 1, Line 1 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Below is from the SQL Help files on INSERT INTO http://msdn2.microsoft.com/en-us/library/ms174335.aspx
What is the N doing in this script? (...N'F2'...N'Square Feet'...)
----------------------------------------- USE AdventureWorks; GO INSERT INTO Production.UnitMeasure VALUES (N'F2', N'Square Feet', GETDATE()); GO -----------------------------------------
Hi there. I have to change the drive letter where SQL server have hisdatfiles (master included). If I do that, ob the SQL fail to start, becouseit's unable to find the master datfiles. Is it possible changing driverletter without reinstalling the complete product?BRamnésia
Here is my question: Can you set this code up to do more than one word ex: I have names like Mc Donald, or Brook-Smith and the are lower case like the code states??
upper(left(client.first,1))+ lower(right(client.first,len(client.first)-1)) +' '+ upper(left(client.last,1))+ lower(right(client.last,len(client.last)-1)) As 'Full Name'
I am looking for a way to, at execution, have 1 single report either print in "letter" or "A4" depending on a parameter I send it... anyone have an idea on where to start on this?
Hi all!This is the problem:User enters student name, in the database, only the 1st letter is uppercase and the rest is in Lower case.So, I want to fix this so as it is not case sensitive, i.e. the user can enter a name and it will return the recordwhether they enter it in upper or lower case.My Code:CvtUpperCase.Text = UCase(Content.Text) //stores user's input Select Case OptionChoice Case "Student_FirstName" MyCommand = New SqlDataAdapter("select * from [qryStudentDetails] where [qryStudentDetails].[Student_FirstName] like '" & CvtUpperCase.Text & "'" , myConnection) ....Any ideas??
Hi am new to this, I am doing some asp development and wanted to know if there is a way of specifying the drive to place log and data devices through sql statments or stored procedures.
I'm looking to get the drive letter and path for my backups programmatically. I am monitoring my sqlserver boxes remotely and need to get this information to determine if my backups will fit on the backup drive.
I'm looking for either a sql statement or a stored procedure to accomplish this.
For example...the backup script states: EXECUTE master.dbo.xp_create_subdir N'H:MSSQLBackupmaster'
I'm trying to think a way to map an average rating (1-10) to a letter grade (A-F).
For example, if I querry the name of a professor and the corresponding rating for the professor, then I would also like to generate a column displaying the corresponding letter grade for the professor based on the rating.
Professor | Rating | Grade |
Smith 8.5 B
I use an aggregate function to diplsay the rating. I'm thinking I may need to write my own aggregate function to display the letter grade. 'Professor' is an actual field of the database.
