How To Replace Ucase() Fuction In SQL Server
Feb 15, 2005an example will be appriciated!!!
View 4 Repliesan example will be appriciated!!!
View 4 RepliesPlease provide me with this queries solution
"select leadsource_id from Leadsource_Master where trim(ucase(leadsource_name))='" & Address_Module.name_check(Trim(UCase(cmbLeadSource ))) & "'"
It is a query in access, I want to use the same in SQL SERVER 2000 using VB 6.0
Hello All,
Let me fitst say that I have found this forum to be VERY good and the members have an excelent knowledgebase.
I am very impressed and appreciate all of the help.
I have a table in my SQL database that I need to modify a little and which contains a lot of data.
My question is in wondering if it is possible to write a function that would loop through all of the rows in a particular column field and update the values?
What I mean is that I have a few columns (fields) in that table that hold numeric values like (1, 2, 3, 4, 5, .......) and I need to loop through all of the entries in that table to add 10000 to each one and store the results back into that same table.
Example is to replace 1 >> 10001, 2 >> 10002, 3 >> 10003, etc.....
Could some one please tall me how this can be done and what I need to do?
Thanks,
Lonnie
I have a column in a table I want to convert all data to UCASE. Currently I have some that are lower case, some Uppercase, and some mixed. Considering there are over 700,000 records and these are just manufacturer names, I want to convert these to uppercase so all displayed information is consistent and I do not have to go through my scripts and convert to UCASE anywhere I pull a manufacturer name.
Is it possible to update a column data to UCASE? I understand how to do this via a row and with a condition, but an entire column has me confused.
Thanks for your help.
Hello, everyone!Does anyone know how I can pull additional field in a database whenthe max() of one field is pulled. For example:================================================== ===SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) ASmax_recordcreateddatetimeFROM table1GROUP BY foreign_id================================================== ===Here I am trying to pull the records that have the latest date foreach foreign_id. The result set above will pull foreign_id andmax_recordcreateddatetime but I needed to also have it display onemore field, current_status like this:================================================== ===SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) ASmax_recordcreateddatetime, current_statusFROM table1GROUP BY foreign_id================================================== ===The problem however is that SQL wants me to add this field to GROUP BYor use an aggregate function with it and I don't want any aggregatefunction processing - I just want current_status to show up there forme to see. How do I do this?Thank you for any input in advance!Roumen.
View 3 Replies View RelatedHi All
I am new to SSIS...
I have a function that I want to apply to many (around 50) columns in a Text File (Input)
Can I use my function in defining the Expression of all those columns?
TIA
Hi, I am trying to incorporate a cursor into a table function so that i can use the function to insert values inot a table. Everytime i add the INSERT INTO @MyTable syntax then the cursor seems to start an endless loop.
Does anyone have any ideas for me?
Code Block
ALTER PROC csp_ICASTransaction_AskDoc
@ClientID INT
AS
DECLARE @ClientID INT
SET @ClientID = 1
DECLARE @cClientID INT
DECLARE @cMonth VARCHAR(20)
DECLARE @cOccurance INT
DECLARE @Counter INT
DECLARE @AskDoc CURSOR
SET @AskDoc = Cursor FOR
select @ClientID, Month_Year, SUM(AskDocs)
FROM zzenrolled$
WHERE ICASClientID = @ClientID
GROUP BY Month_Year
OPEN @AskDoc
FETCH NEXT FROM @AskDoc
INTO @cClientID, @cMonth, @cOccurance
/*
SELECT @@Cursor_Rows
PRINT @cClientID
PRINT @cMonth
PRINT @cOccurance
*/
DECLARE @MyTable TABLE (ClientID INT, Date DATETIME, Occurance INT)
WHILE (@@FETCH_Status = 0)
BEGIN
SET @Counter = 0
INSERT INTO @MyTable
VALUES (@cClientID, CASE @cMonth
WHEN 'Jan-03' THEN '20030101'
WHEN 'Feb-03' THEN '20030201'
WHEN 'Mar-03' THEN '20030301'
WHEN 'Apr-03' THEN '20030401'
WHEN 'May-03' THEN '20030501'
WHEN 'Jun-03' THEN '20030601'
WHEN 'Jul-03' THEN '20030701'
WHEN 'Aug-03' THEN '20030801'
WHEN 'Sep-03' THEN '20030901'
WHEN 'Oct-03' THEN '20031001'
WHEN 'Nov-03' THEN '20031101'
WHEN 'Dec-03' THEN '20031201'
WHEN 'Jan-04' THEN '20040101'
WHEN 'Feb-04' THEN '20040201'
WHEN 'Mar-04' THEN '20040301'
WHEN 'Apr-04' THEN '20040401'
WHEN 'May-04' THEN '20040501'
WHEN 'Jun-04' THEN '20040601'
WHEN 'Jul-04' THEN '20040701'
WHEN 'Aug-04' THEN '20040801'
WHEN 'Sep-04' THEN '20040901'
WHEN 'Oct-04' THEN '20041001'
WHEN 'Nov-04' THEN '20041101'
WHEN 'Dec-04' THEN '20041201'
WHEN 'Jan-05' THEN '20050101'
WHEN 'Feb-05' THEN '20050201'
WHEN 'Mar-05' THEN '20050301'
WHEN 'Apr-05' THEN '20050401'
WHEN 'May-05' THEN '20050501'
WHEN 'Jun-05' THEN '20050601'
WHEN 'Jul-05' THEN '20050701'
WHEN 'Aug-05' THEN '20050801'
WHEN 'Sep-05' THEN '20050901'
WHEN 'Oct-05' THEN '20051001'
WHEN 'Nov-05' THEN '20051101'
WHEN 'Dec-05' THEN '20051201'
WHEN 'Jan-06' THEN '20060101'
WHEN 'Feb-06' THEN '20060201'
WHEN 'Mar-06' THEN '20060301'
WHEN 'Apr-06' THEN '20060401'
WHEN 'May-06' THEN '20060501'
WHEN 'Jun-06' THEN '20060601'
WHEN 'Jul-06' THEN '20060701'
WHEN 'Aug-06' THEN '20060801'
WHEN 'Sep-06' THEN '20060901'
WHEN 'Oct-06' THEN '20061001'
WHEN 'Nov-06' THEN '20061101'
WHEN 'Dec-06' THEN '20061201'
WHEN 'Jan-07' THEN '20070101'
WHEN 'Feb-07' THEN '20070201'
WHEN 'Mar-07' THEN '20070301'
WHEN 'Apr-07' THEN '20070401'
WHEN 'May-07' THEN '20070501'
WHEN 'Jun-07' THEN '20070601'
WHEN 'Jul-07' THEN '20070701'
WHEN 'Aug-07' THEN '20070801'
WHEN 'Sep-07' THEN '20070901'
WHEN 'Oct-07' THEN '20071001'
WHEN 'Nov-07' THEN '20071101'
WHEN 'Dec-07' THEN '20071201'
END ,'3' )
SET @Counter = @Counter + 1
IF @cOccurance > @Counter
FETCH NEXT FROM @AskDoc
INTO @cClientID, @cMonth, @cOccurance
END
CLOSE @AskDoc
--INSERT INTO [Transactional$New] (ClientID, [ Date], [ Occurance])
SELECT * FROM @MyTable
DEALLOCATE @AskDoc
I want the Cursor to insert the values into the table that i commented out, but it doesn't seem to insert the values, it just loops.
Any help will be greatly appreaciated.
Kind Regards
Carel Greaves
HI All,
Is there any way to pass report dataset to Custom function as object?
Regards
Sith.
I have a table which has six fields ID, dateDue, dateReceived, dueday. month, ContactFYEmy query looks likeselect ID, DateDue, dateReceived, dueday, month, ContactFYEfrom Reportwhere (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )user defined functionCREATE FUNCTION dbo.udfDisplayTime ( @dueday int, @month int, @ContactFYE smalldatetime) RETURNS smalldatetime AS BEGIN DECLARE @ReturnString AS smalldatetime Declare @dueday1 as int if (@month =1)beginset @dueday1 = @dueday -7endelse if (@month =2)begin set @dueday1 = @dueday -14end else if (@month =3)begin set @dueday1 = @dueday -60endselect @ReturnString = DATEADD ( dd, @dueday1, @ContactFYE) Return @ReturnStringENDI got a incorrect result when using this query and user defined function.the result that I got was out of between displaytime and datdue. any idea for this , Thanks.
View 2 Replies View RelatedI'm moving some queries out of an Access front end and creating views out ofthem in SQL Server 2005 express. In some of the numeric fields, I use nzquite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.Is there anything equivalent to this in SQL Server? Right now I'm usingCASE WHEN ... but it seems like an awful lot of script to write just toreplace null with a zero.Any help would be greatly appreciated.Thanks!
View 16 Replies View RelatedI want to replace my Windows 2000 SQL Server 2000 with a new beefier machine. And I really don't want to change all the setups on the users PCs to point to this new machine. So, I want the new machine to have the same name as the old machine. I think that Windows actually uses SIDs and not the name so I'm wondering what problems I may cause by just naming the new machine the same as the old and replacing?
My guess is that this is done everyday. Does anyone know where I can find information on how to do this safely?
Thanks
I found a search-and-replace program for SQL server that works GREAT, but I have a list of about 200 words to search for, with corresponding replacements. Rather than editing the code below for *each* word and running it 200 separate times, I'd like to iterate through the list, but my MS-SQL programming skills are...light (to say the least). Anyone have any ideas how I can create a list or hash-type variable and use the code to loop through and do the replacements all at once?
/*
*
* Search & Replace
*
* Use Ctrl+Shift+M to replace template values
*
*/
set xact_abort on
begin tran
declare @otxt varchar(1000)
set @otxt = '<string1, text, text to be replaced>'
declare @ntxt varchar(1000)
set @ntxt = '<string2, text, replacing text>'
declare @txtlen int
set @txtlen = len(@otxt)
declare @ptr binary(16)
declare @pos int
declare @id int
declare curs cursor local fast_forward
for
select
id,
textptr(<field_name, sysname, target text field>),
charindex(@otxt, <field_name, sysname, target text field>)-1
from
<table_name, sysname, target table>
where
<field_name, sysname, target text field>
like
'%' + @otxt +'%'
open curs
fetch next from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin
print 'Text found in row id=' + cast(@id as varchar) + ' at pos=' + cast(@pos as varchar)
updatetext <table_name, sysname, target table> .<field_name, sysname, target text field> @ptr @pos @txtlen @ntxt
fetch next from curs into @id, @ptr, @pos
end
close curs
deallocate curs
commit tran
I have to periodically overwrite selected tables in one environment (Prod) with the contents of the same named tables from another environment (Staging).
There are about 200 tables involved.
In SQL Server what would be the best way to do this and minimize logging?
Thanks in advance.
Gerry
Is it possible to replace while loop by CTE?
I will fetch records from table 1 based on tretmentid.
get the count of records
run the while loop
inside loop I will compare the column values with the same table(table 1) for different tretmentid.
if any one row matches will come out from loop and send status .
can it be replaced by CTE ?
I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.
View 8 Replies View RelatedWhat is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.
I have the below table :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
Then i need to replace second row with value with first row with null and so on :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL
I have a separate list of calendar years with radiocarbon year equivalents in SQL server but no conversion equation. Most but not all of the data I have is in radiocarbon years. I thought at first I could just link the tables but I don't want the data that is already in calendar years to be linked to this conversion table. Is there any way I can either link the two tables with criteria for which data is linked (Only ages that are in radiocarbon years). Or possibly a way to query all ages that are in radiocarbon years and do something similar to a find and replace with a large list of numbers to change?
View 14 Replies View RelatedI have a SQL Server 2005 database that has a table with a TEXT column. This TEXT column has XML data in it. The length of the XML data in each record in the table is about 700,000 characters. What is the quickest most efficient method to replace a nodes text with another value? I.E., <LogoLarge>aasdfasdfaasadfasdfsdfasdfadsf</LogoLarge> with <LogoLarge>a</LogoLarge>. This table has about 2 million records. Thanks in advance for your help.
View 2 Replies View RelatedMy existing SQL server is an old box that can no longer cope with the load. I need to replace it with a brand new box.
I am looking for a comprehensive set of steps that I could follow to ensure that the transition to the new server goes well.
I'm looking to transfer all my user databases but I'm not so sure whether I have to backup and restore my system databases to the new server as well.
I want to name the new server the same as the old server since I will be taking the old server out of production but I'm not sure whether I should rename the server AFTER I move over all the database and how that would affect SQL. Or should I first rename the old server, remove it out of the domain, then name my new server with the same name as my old server, and then proceed with the SQL installation and the moving of the databases.
Also, I do have a maintenance plan, DTS packages and some jobs set up on my old server that I would also like to move to the new server.
Is there a web page that explains all the steps in granular detail ?
hi all,i was using the system tables namely sysindexes and sysobjects in sql server 2000. But now sql server 2005 is using instead of sql server 2000. Since am using sysindexes and sysobjects, too much time is taking for the execution in sql server 2005. So I need to change the query suitable for sql server 2005.I have read in msdn that the system tables are replaced with corresponding catalog views in sql server 2005. The catelog view for sysobjects is sys.objects. plz check the link http://technet.microsoft.com/en-us/library/ms187997.aspxCould anyone please tell me how to replace the sysindexes and sysobjects in the query without rewriting the query too much. If I can replacesysobjects with just sys.objects, then it will be very helpful. Is it possible? And what about sysindexes. Any help anyone could give would be greatly appreciated!
View 8 Replies View RelatedHow to replace DateTime field with null value in SQL 2005 server
I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value
create aa @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate
There are plenty of scripts to do this on a per-DB level, but any that will allow me to generate a script for all DB's at once? Mine are split across dozens and it would be much easier to do a loop (using MS_ForeachDB ? )
View 1 Replies View RelatedI would like to replace the value in the Select query of CO OWNER with a space ' ' or NULL if the ORDINAL value = 0.
The CO OWNER name is stored in the CARDNAME table, and the OWNER name is stored in the NAME table. I can not change the db structure.
JEAN is the ACCOUNT owner and BILL is CO OWNER of two cards.
Current Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 BILL JEAN
Desired Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 NULL JEAN
Current SQL Select statement:
SELECT DISTINCT
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST AS CO_OWNER,
[code]....
using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.
select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns
I need one query...
create table #task(TaskId bigint unique, Name varchar(2000))
insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
-- select * from #task
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000)
Return table
as
return
(
with data as
[Code] ....
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().
SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A
I am trying to replace names found in 'xml' field name as hyperlinks, by matching with the names in database_tags.I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
[code]...
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.Is there a way to get the function to recognize more than one names as hyperlinks.
I'm trying to replace a table name in 250 stored procedures. I found this script below which does a good job but it also finds tables with similar names. How can I limit the replacement to the exact table name? If my original table name is MyTable001, I do not want to find MyTable001_ID.
-- set "Result to Text" mode by pressing Ctrl+T
SET NOCOUNT ON
DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-- text to search for
SET @searchFor = 'MyTable001'
-- text to replace with
SET @replaceWith = '[MyTable002]'
[code].....
I'm attempting to improve a vendor created query.
Here is the original -
SELECT m.lID
FROM Message m
inner join Message_Cc mCC on m.lID=mCC.lMessage and mCC.lOfficeRecipient = 200321
INNER JOIN UserRole d on mCC.szRecipient=d.szRoleName
inner Join Map_UserAtOfficeToRole a2 on a2.lUserRole = d.lid AND d.nRecordStatus = 1
[Code] ....
If I run this without the LEFT OUTER JOIN and the is null statement I get 648 rows. But If I include it I get 0 rows. I can't understand why I get 0 rows with the outer join.
We have a legacy database that have hundreds of stored procedures.
The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.
I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.
I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?
I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?
View 5 Replies View Relatedhow we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))
[code]....