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
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.
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 table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?
View 3 Replies View RelatedI am new to sql server and I am having deficulties writing sql script to perform the following:
1) Merging data from two tables A and B
2) Eliminate duplicate present in table B (Conditions to satisfy for dublicate:If similar address is found in both tables AND class type in Table A =1
3) merge data related to dup(eliminated records) to new table.
Not sure if we can eliminate records first before merging two tables. Tables are as follow:
Table A
Fields: ID, NAME, Address, city, zip, Class type
Value:123, John, 123 Main, NY, 71690,1
Value:124, Tom, 100 State, LA, 91070,0
Table B
Field: ID, NAME, Address, city, zip, Class Type
Value:200, Tim, 123 Main, NY, 71690,0 (duplicate; satisfied both conditions and left out in final table)
Value:124, Jack, 100 State, LA, 91070,0 (same condition but second condition is not met)
Value:320,Bob, 344 coast hwy, slc, 807760,0
Final Table:
Field: ID, NAME, Address, city, zip, Class Type
Value:123, John, 123 Main, NY, 71690,1 (should also show t
Value:124, Tom, 100 State, LA, 91070,0
Value:124, Jack, 100 State, LA, 91070,0
Value:320,Bob, 344 coast hwy, slc, 807760,0
Table d:(relate to table A:showing all products that are related to table A)
table_A.ID, Products
123, Paper 1
123, paper 2
Table e:(relate to table B: showing all products that are related to table B)
table_B.ID, Products
200, Paper 3
Final Table:
ID, Product
123, Paper 1
123, Paper 2
123, Paper 3 (changing table b id to table a)
Would appreciate any help writing script to perform such transformation. Thanks
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 RelatedI 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.
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
View 3 Replies View RelatedHi;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 RelatedHi:I have written a SQL statement that accepts a letter and then prints out all the records in a table starting with that letter. I was wondering if there is a way that I could change the query so that if prints out all records if a blank or empty value is passed in?Here's my query: ALTER PROCEDURE [dbo].[GetMediaListByFirstLetter] ( @firstLetter char(1))AS SELECT Media_ID, OrgName FROM Media WHERE UPPER(SUBSTRING(Media.OrgName,1,1)) = @firstLetterAny help doing this would be greatly appreciated.Roger
View 5 Replies View RelatedWe imported approximately 2.9 million records from our mainframe server
into our SQL Server but have run into a problem. The data in a
few of the fields contains both leading and trailing spaces. An
example of the data would be like this, using periods to represent
spaces:
What we have:
..1A02938.....
What we need:
1A02938 (no spaces)
Is there some sort of algorithm I can run on the data to remove
those spaces? The problem is coming up when trying to perform a
SELECT query. We try something like:
SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938" but we get zero
results because of the spaces in the database. The datatype of
the filed is char(20) because we need some flexibility on the size of
the data stored.
Any assistance would be greatly appreciated.
I want to be able to use a query to display all the records in the 6.5 database that have no data in the STATUS field. This is the query I thought would work....."SELECT * from travel_date WHERE status="''"
But, that is not working. Can someone please help me figure out the right way to wrtie this?
I appreciate your help!
hi
I have data in two tables.
NAMES
IDName
1FIRST
2SECOND
CODES
IDCodeTypeCode
1Axyz
1Babc
1Cgfd
2Axdz
2Bdca
I want to join the two tables to add the Code of CodeType "C" to the records of NAMES
Result Example
IDNameCode
1FIRSTgfd
2SECOND----
I want to have all records from the names with the codetype C, if there is no record with the codetype c for a given ID, the cell should be blank to identify for which ID's the CodeType C is mising.
how should the sql statement look like?
Please help!
thanks in advance!
Mikk
In my report I want an optional parameter to filter all records with a specific field that is not blank. I tried several scenario's without result...
In the parameter I want to set a text value like "exampletext".
In the filter I want a check: if the parameter value is "exampletext", only show the records where field "abc" is not blank.
On the tab Filters from the Table properties I can set three values: Expression, Operator and Value.
Please help!
Originally, I hit this problem with PHP. But then I was able to reproduce using VBScript.
I have a table with a couple of text fields, and one Real field in a table. One row of data in the table. Both text fields set, but the real field doesn't even need to have a value set.
Connecting to SQL 2005 using a DSN - ODBC.
I'm using a stored procedure I named ExecIt that runs a SQL statement that is sent in and returns records.
If I use cursor location adUseClient, it returns values for the text fields.
If I use asUseServer, it returns blank values.
If I use a SQL statement directly, it works. Only fails when using SQL statement by way of ExecIt.
In each case, cursor type is adOpenStatic or adOpenDynamic.
Using this combination fo cursor type and cursor location, it mimics what I see in SQL profiler when PHP is using ODBC.
Anyone have a solution?
Table:
CREATE TABLE [dbo].[test3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[text1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[text2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[real1] [real] NULL,
CONSTRAINT [PK_ReworkLog2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Stored Procedure - ExecIt:
create PROCEDURE [dbo].[ExecIt]
-- Add the parameters for the stored procedure here
@Statement nvarchar(max)
AS
BEGIN
exec (@Statement)
END
DSN has been set up through ODBC manager. Can be using 2005 native driver, or older sql server driver.
Code:
dim txtOut
set conn1=CreateObject("ADODB.Connection")
conn1.Open "my-DSN","my-userid","my-password"
sql="exec execit 'select * from test3 where ID=1'"
set rs = CreateObject("ADODB.recordset")
rs.CursorLocation = 2 'adUseServer - 2, adUseClient - 3
rs.CursorType = 3 'adOpenStatic
rs.Open sql,conn1
if not rs.EOF then
txtOut = "Results" & vbcrlf
txtOut = txtOut &"text1:" & rs("text1") & vbcrlf
txtOut = txtOut &"text2:" & rs("text2") & vbcrlf
txtOut = txtOut & vbcrlf
end if
msgbox txtOut
rs.Close
conn1.Close
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example:
Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
I am trying to bcp import a text file into a SQL Server 2000 database.The text file is coming out of a java application where orderinformation is written to the text file. Each record is on it's ownrow, so the last item in each record has a new line character at theend of it to create the next row. This works well in creating the filehowever bcp does not like to import this text file with the extra blankline at the end. If I change the new line character to the beginning ofthe records then there is a blank line at the top of the text file,which bcp also does not like. Does anyone have any suggestions for meto get around this issue?Thanks,
View 6 Replies View RelatedSeveral textboxes are being on a web form for date entry. and we want to allow the user to leave date fields blank (if particular date is unknown). However, when the field is left blank, the SQL Server 2000 database defaults to 1/1/1900 (datatype = datetime).My question is as follows: How is it possible to leave a date textbox field blank, and either send 00 0 000 or spaces to the SQL Server 2000 database datetime field?Any advice/insight is appreciated! Rob
View 2 Replies View Relatedi have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:
Code:
IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table
but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"
thanks
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:
IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table
but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"
thanks
I wanted to remove duplicate records from SSRS report. I set the "Hide Duplicates" to True. It is now working, But i am getting the space between the two records, which i want to get rid of. How to get rid of extra spaces between two records ( Please find the details below).
View 5 Replies View RelatedHi every one,
I am facing problem in printing the reports from browser and also when i export it to pdf,the problem i am facing is blank pages are coming when report column getting the large amount of text around 2500 characters into column value.
can any one help me in this issue?. if the report is getting acceptable amout of data it is printing in proper way i.e no balnk pages at all.i maintained all properties like margins+body size < page size.
Hi,
I receive blanks for a column called value and i need to represent it as a blank or NA into a colum whose datatype is float in the datawarehouse.
how is this possible because in current schenario a blank is being converted to 0
which is not the right thing.
i would like to retain the blank in conversion from char to float ,
please explain me if it can be done or how to overcome this issue.
Thanks
I have to create a search textbox where if i key in the word "dog",it will search against a field called "Name" in a table.Then it will return all records where the text/data in the "Name" field contains the word "dog".For example,it will return the records where the text/data in the "Name" field has words such as "hotdog","doggie","dog barking","big dog" etc etc.
Can i use simple SQL for this or do i need to configure the full text search service on SQL server 2000 and use the FREETEXT predicate?Thank you in advance for any replies.
I'm using DTS to export a table. I need each record to be on a separate line, but I have a description field that is quite lengthy so it's not fitting on one line. Can someone advise how to accomplish each record being on only one line? :)
View 3 Replies View RelatedHi, I am having three text box which accepts user data & insert that in sql database. But I am not able to do this , I think this is the simplest of all . Can somebody plz tell me how this can be done from scratch that is connection string & settings in web.config ? Plz guide using C#ThanksRegards,-Sunny,
View 18 Replies View RelatedHi all,
I am designing a DTS package instead of SSIS.
I have a text file and already uploaded to tableA, there is a field named NameID in tableA. The field NameID should match the NameID in tableB and update other fields of tableA, the non-match records will generate another exception text file.
How can i implatement this in DTS? Which task or tech?
thanks
Micror
Hope I am in the correct section.
I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in a
common word like "damage" I get like 32,000 records. Now, the
server handles those records in about one second but the ASP page that
returns the results takes about one MINUTE to download. When I
save the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
Thanks!
cbmeeks
Hi, got a problem and can't figure this one out.
basically i've got a field containing a value '(14)(12)(33)(22)' and i want to compare it to a table containing those values in separate cells...
record 1 : (01)
record 2 : (02)
etc...
and i want to compare this one field to those records to see whether that text string contains anything the table contains.
i've tried ContainsTable & IN but still can't figure this out.
any help greatly appreciated
:o
All rows of a 1500+ record text file we have at work should be 512 spaces. Some are not and seem to be causing problems. I get half the records I should be getting on an import. When the DTS task comes across one of these records it takes it and appends them together and makes two rows one row. Any ideas? I tried making a table with one field of datatype char(1000) and tried to import into this table. I figured this would add any missing trailing spaces but no go.
ddave
Hi
I have a new client with an existing system that has just over 2 million business listings in one table. Each business listing is associated with one business category.
* Company Table (around 20 fields):
companyID
companyName
categoryID
state
postCode
etc.
* Category Table (5 fields)
categoryID
categoryName
etc.
We are using MSSQL 2005 Express Edition with Advanced Services
A free text search needs to be performed on the companyName and categoryName limited by region (state and or postcode).
1) What kind of response times should I expect for the free text search (I have not used the free text search before)
2) How should I index the companyName and categoryName so they are both used in a joined query? i.e. Do I just configure the free text search index on each field separately and it should work?
Any suggestions appreciated.
Best Regards
Kevan
I have written up a grid consisting of properties and units.The way it works is we have properties, and within properties there are units. They are two seperate tables.Some properties do not have any units so in the unit reference (UN_UREF) column for those records which do not have units and are NULL I would like to insert the text 'NO UNIT'.Please see below for my SQL for the grid which works fine.
create or replace view VWC_PROPMKUNIT_TEST AS ( SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, UN_UREF, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF);
All I need to do now is insert 'NO UNIT' within the Unit Reference column where it is NULL.
Greetings,A novice needs some advice:I am able to bulk insert data from a text file into a table. Is itpossible to run a query and insert the results into a text file on thelocal or network hard drive?Thanks For Reading,Aaron
View 2 Replies View Related