Hi
I have a table with column type as ntext. I need to modify the column value. I wanted to replace a given characterstring with another one in this column. Any assistance on this is highly appreciated.
Thanks!
Santhosh
first of all... hello, forum! im a total newbie
ive been working on this script today and cannot for the life of me get the following employee name format correct...
the data is currently "lastname~firstname" in employee table... i am trying to write a select statement that will take that and remove the "~" (tilde) and replace it with a "," (comma).
how do i get "lastname~firstname" from employee table to display in "lastname,firstname" format?
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.
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 RelatedI have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And I need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
I'd like to return the left-most character from an 8 character string & the third from the left character too.
Like this ABC00123 returns AC
$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());
echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){
[Code] ....
Hi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());
Hi, I'm trying to conver an Access database to SQL 7, but I can't find a easy way to replicate the aggregate function FIRST in SQL, can anyone give me some advise on how to "fake" the FIRST agg function in SQL7? Thanks
View 1 Replies View RelatedI am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash I know to replace it with a double backslash \. But what if it is a " double quote what do I need to do to get it to appear as is?
Thanks
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.
Nevil Mascarenhas writes "Hi SQL team,
I am just a beginer in SQL
Here is sample output of a SQL query
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD
5453 QWE 22-08-05 01:00
In this above example, I would like to replace blank fields in Startdate and Starttime coloumn with XXX
Output required is
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD XXX XXX
5453 QWE 22-08-05 01:00
Can you please guide me."
Hi i have 2 cols
col1 col2
A1 21
A2 22
A3 21
A4 23
I want to create a report such that i should see all values of col1 but in col2 i want to show only value of 21 and the remaining should be zero.
So basically all the other values except 21 should be zero in the report but should not be updated in the database.
So the report should look like
col1 col2
A1 21
A2 0
A3 21
A4 0
can you please tell me how to do that
Thanks
vic
Vicky
Hi,Is it possible to do from one script? We have a set of user's tables like"tbl%". We can get this list very easy using this script:SELECT name FROM sysobjects WHERE xtype = 'U' AND name LIKE 'tbl%' ORDER BYname;We need to change some column names if these names are in a special listthat we have. What can we do? Use FOR EACH ROW? Or what?So, I need to get a column list for each table and check if every columnname is equal to one of the names from the list and then if YES replace itby something or add some symbol to this name. Terrible or possible?Regards,Dmitri
View 4 Replies View RelatedHi all,
We are trying to migrate a SQL Server 2005 database/schema onto SQL CE. The original SQL Server 2005 schema uses views. And VIEWs are not supported on SQL CE. What can we use as an alternative?
Here are the option I can think of:
Use Tables instead of views : We are currently using this approach. But the disadvantage is that data remains on the DB. We need to clean and reuse the table everytimeCan we use a DataSet or DataTable instead? Any sample code available?
Can we replace them by some in-memory data structure?
Hi all
I need to remove all the ' in a table and i am having some problems with the script, any help would be great
Thanks
Richard
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.
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
I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
ITEMSitemID,itemName,itemDescriptionetc...
ORDERITEMSOrderLineID,OrderID,itemID
ORDERSOrderID,OrderCompleted
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!
Ryan
Hi, below is the query that i use.update user set user_description=' "+ userDesc +" ' where user_id = 1;userDesc is the value taken from a textbox and is supposed to be used with the update query.userDesc = "kad'nsad'kasnd'nak";The quotes in userDesc is affecting the update. Is there anyway to replace quotes with ' using stringbuilder?
View 1 Replies View RelatedHi, I have the following query.
sql = "select firstname AS Expr1, lastname AS Expr2, status AS Expr3 from person order by lastname"
Status is either 0,1,2 or 3
How can I use the query to create "a temp Alias" for the query, so that there is a "temp Alias" AS Expr4, AS Expr5, AS Expr6 and AS Expr7.
So if status = 0 then Expr4 = "true" else false
So if status = 1 then Expr5 = "true" else false
So if status = 2 then Expr6 = "true" else false
So if status = 3 then Expr7 = "true" else false
So when the reader reads Expr4 its either true or false.
Is this possible i a query?
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
?
Hi,
how can I remove newline characters and tabs from a char field in sql.
We are directing the results from a query to an excel and having the newlines in one of the columns is messing up the whole output.
thanks
Zoey
I am thrawn at the deep end
I have been given the task of moving certain tables (that have PK, FK Constraints) from an existing Database (A) to form another Database (B) but making sure all the references to other tables in the original DB (A) are not affected. I was talled that triggers can solve this but I am not sure where to start. The Question is what I am looking for to replace, and how ??
Please help !!!!!!
Saad
Need to update data from one table with data from another table.
I'm very, very new to MS SQL Server
I have the following two tables.
First table
tblEmp
Field - empno
Second table
tbltemp
Field - oldempno
Field - newempno
tblemp.empno = tbltemp.oldemno
I need to replace the data in tblEmp.empno with tbltemp.newempno where tblemp.empno = tbltemp.oldempo.
Thank you.
Hi, and tanks for taking time to read this question. I am still a novice at SQL (Server 2000)programming.
Is there a way to replace the contents of a column of a table derivied from SELECT and WHERE statements?
For example,
SELECT * FROM table1
WHERE column1 = 'text'
and replace 'text' with say, 'newtext'
Any help would be appreciated!
Regards,
Chandran
I need to change the first digit (0) in a number, like 05678494 and replace it with 0046 so it would look like 00465678494 when it is done.
I have tried to use the REPLACE function without any success.
Can anyone help me?
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
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've got a sproc that we are migrating from SS2K that uses the reserved keyword 'Pivot'. Best way to replace it. I think it is renameable until the select from TempDB (which is mysterious as I can't see a table in tempDB by that name!!) as below...
CREATE PROCEDURE [dbo].[sp_CreateXtab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@SQLOutput varchar(8000) OUTPUT
[code]....
Hi friends
I have a database where i need to replace with a new version of the same one.
Can anyone suggest the best possible and reliable way of doing that
regards
Vic
Vicky
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!!!