How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)
Just a couple of typical examples of how the data should appear ~
WHEN 'DAYS' THEN DATEDIFF(dd, CHECKOUT_TS, GETDATE())
ELSE DELIVER_ID END
FROM REQUEST
If @sortcolumn has 'DATE' or 'DAYS' it runs fine....any other value, I get an exception "Conversion failed when converting datetime from character string". All columns except CHECKOUT_TS are of type varchar.
First off, I know this is a presentation issue. Second, no, I can't force a change on my source systems.
Some of the systems that send my BI application data, send that data in all upper case like so "JOHN DOE". We have this horrible SQL function that goes through and makes sure that the first letter in a word is always uppercase and the rest of the letters are lower case. So my results are "John Doe".
As you can imagine this is dreadfully slow when executed a couple of hundred million times, but what are my options?
I have not used Data Quality Services yet, but the chart in BOL says a DQS SSIS cleansing task can do 1 million records in 2 hours on a given set of hardware. That is still pretty horrible.
I suppose I could cobble together a Script task in SSIS, but I am pretty sure clumsy dotNet is not going to be much faster.
CREATE FUNCTION [dbo].[udf_ProperCase](@UnCased varchar(max)) RETURNS varchar(max) as begin declare @Reset bit; declare @Ret varchar(max);
I'd like to know how I can adapt this function so it will convert a scottish/irish surname (McDonald or O'Shea) when there is only the surname in the column
This is what I'd been using for multiple words (Ronald McDonald). But it won't work on just Mcdonald. I'm sure it's just a simple tweak, but it all looks Punjabi to me?
Thanks in advance!!
CREATE FUNCTION [dbo].[f_ProperCase](@Text as varchar(512)) RETURNS varchar(512) as BEGIN
DECLARE @Reset bit DECLARE @Ret varchar(512) DECLARE @i int DECLARE @c char(1)
SELECT @Reset = 1, @i=1, @Ret = ''
WHILE @i <= LEN(@Text) SELECT @c= SUBSTRING(@Text,@i,1), @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END, @Reset= CASE WHEN CASE WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [DOL]''' THEN 1 WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [D][I]' THEN 1 WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [M][C]' THEN 1 ELSE 0 END = 1 THEN 1 ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0 ELSE 1 END END, @i = @i +1 RETURN @Ret
-- Test: SELECT dbo.f_ProperCase('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence) trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test.' ) END
Hello:Is it possible to use CASE expression in AND condition? i.e.------------------------------------------CREATE PROC spBlah( @id INT, @val INT)ASSELECT * FROM aTableWHERE tableID = @idAND ( CASE @val WHEN 1 THEN otherCol = someValue END CASE @val WHEN 2 THEN otherCol != someOtherVlaue END )-------------------------------------------
I there a way to differ the filtering field through a Case expression in the Where claus? ex: Where Case When @StuNum = '' Then S.SSN = @SSN Else S.StuNum = @StuNum End And ... The actual field to filter by differs, but I can't seem to find the right syntax to do this. Any help is appreciated : )
The SUBSTRING function returns @domain.com. I don't want any results to be displayed from my sql statement if it equals @myemail.com. I have other cases too that are not relevant to my question. I'm getting an error that says invalid syntax near <
SELECT DISTINCT CASE CUST_EMLAD_TX WHEN SUBSTRING(CUST_EMLAD_TX, CHARINDEX('@',CUST_EMLAD_TX), LEN(CUST_EMLAD_TX)) <> '@myemail.com' THEN CUST_EMLAD_TX END FROM ...
In the Portal1 case expression in the script at the bottom I would liketo replace where the result 1 is returned, with the substring functionreturned as Portal{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}However, I am experiencing errors. I think it is because The substringfunction will not return a number as the case expression expects so Imust incorporate cast or convert, but do not know how. Can you help???SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',Field1) - 8)- (CHARINDEX('tonep', Field1) + 4)) AS portal,Table.*FROM Table
I'm trying to write a case expression similar to the expression listed below. I basically want to compare the Anniversary date to the current date. Whenever I write the expression like this I get a syntax error.
Msg 102, Level 15, State 1, Line 147
Incorrect syntax near '<'.
Case AnniversaryDate
WHEN AnniversaryDate < GETDATE() Then 'Not Yet'
WHEN AnniversaryDate = GETDATE() Then 'Now"
ELSE 'You Missed it'
END AS Anniversary
Could someone please guide me in the correct syntax for this expression. Thanks
I would appreciate assistance as to how I should correct the following CASE expression::
BilledCarrier = CASE LEN(cur_billed_carrier) WHEN >0 THEN cur_billed_carrier ELSE 'Not Billed' END
I want to return the cur_billed_carrier when its length is >0 but I can't find the correct syntax. My attempt returns an error at the '>' operator. Thank you.
insert into---- select ID_NO,cast(row_number() over(partition by ID_NO order by ID_NO)as varchar(2)) from test_222
I am trying to insert into test222 table .The id_no column is varchar field error: Arithmetic overflow error converting expression to data type varchar. The statement has been terminated.
I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!
Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.
Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.
I'm just getting started with SQL 2005 so I don't know how to do this using an expression.
ALTER PROCEDURE dbo.TEST_TOTALCALLS ( @varDate as varchar (255), @StartDate as datetime, @EndDate as datetime ) AS
SELECT CASE @varDate WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) END, COUNT(*) as 'Total Calls' FROM CALLMASTER WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <= @EndDate)
GROUP BY CASE @varDate WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <---this part gave me an error, because of the comma, END ORDER BY CASE @varDate WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME) END
The month case is giving me an error. I think it has to do with two expressions in one line. Anyone know how to combine that into 1 expression? or is there away to work around it? As I would like to display the month as Name, but group and sort by number. Thx!~
I have to use the Case expression in my query, so I search arround the web and got the following: SELECT title, price, Budget = CASE price WHEN price > 20.00 THEN 'Expensive' WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate' WHEN price < 10.00 THEN 'Inexpensive' ELSE 'Unknown' END,FROM titles It should run OK base on my research in the internet. But my SQL Server gave me error: syntax error arround '>'.I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.Thanks!
Hi, Maybe this is just totally wrong but this is what I'm trying to do. I want to dynamically insert a value using a CASE expression while executing an external SP from my current SP. EXEC uspAddMessage @UserId, CASE @MyAction WHEN 'DELETE' THEN 'DELETED TRANSACTION.' WHEN 'APPROVE' THEN 'TRANSACTION APPROVED.' WHEN 'REJECT' THEN 'TRANSACTION REJECTED.' END, @DateStamp Keep in mind that this is in my stored proc that is doing the processing. I'm calling this common proc when I complete my transactions. Thanks, S
Hello everyone, is there anyway to use an update sub query in a case expression , something like this for exampleselect username,(case when password ='606' then (update users set username = 'me' where id= '3') else password end) from users i have been googling this issue just with no usefull resultsthanks for any replyMahmoud Manasrah
I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up. The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser]( [AutoNumber] [int] IDENTITY(1,1) NOT NULL, [SubmittedDate] [datetime] NULL, [FirstName] [varchar](100) NOT NULL, [LastName] [varchar](100) NOT NULL, [Department] [varchar](100) NOT NULL, [StartDate] [datetime] NULL, [DepartDate] [datetime] NULL [RequestedBy] [varchar](100) NOT NULL, [UpdatedBy] [varchar](16) NOT NULL, [CurrentStatus] [varchar](10) NOT NULL , [DateCurrentStatus] [datetime] NULL, [FormType] [varchar](10) NOT NULL, ) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing. I want to do this for each record: Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user Convert Department to a 4 character department number Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser SET DateCurrentStatus = b.DateMostRecent, CurrentStatus = (b.FormType + a.Department CASE WHEN 'Roads Department' THEN '3000' WHEN 'Engineering and Survey Services' THEN '1900' WHEN 'Waste Management' THEN '8999' WHEN 'Kern Air Pollution Control District' THEN '9149' WHEN 'Environmental Health' THEN '4113' WHEN 'Building Inspection' THEN '2625' WHEN 'Animal Control' THEN '2760' WHEN 'Planning Department' THEN '2750' WHEN 'Community and Economic Development' THEN '5940' WHEN 'Resource Management Agency' THEN '2730' WHEN 'Code Compliance' THEN '2620' WHEN 'Roads Kern Regional Transit' THEN '8998' END) FROM HelpDesk_NewUser a JOIN UserMostRecent b ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
Is it possible? I have a request to create a stored proc that willdynamically add a range to a WHERE clause based on a numeric value of acomment type. If the incoming comment type request is say 10, thewhere clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR ifa 20 is passed in the clause would read IN(20,21.....)So I was thinking that a CASE expression within the proc would be thebest way to go, but have had no luck in finding an example or any otherrelated information regarding CASE exp in a proc.TIABill
I have a scenario where the End User is going to select two parameters. @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.
If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.
@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).
@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:
WHERE
gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate) AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END AND gl.Location = @Location
Given a string it should convert it to a proper text.Example: if you passed a string 'Cat in the hat', I want 'Cat In TheHat'Curious about few things, Does sql have Instr OR Split(like VB)functionalityAnybody can help??
Dear all, I am using sql server 2005. I have two field firstname and lastname. Actual requirment is if category is doctor then display name as Dr. name else name but when I use following sql stament Whenever one of the name is null it show name as null that is whenever firstname or lastname is null I get name as null
select @DOCNAME = CASE WHEN DR_CUST_CATEGORY = 1 THEN 'Dr.' + ' ' + dr_cust_name + dr_cust_lastname ELSE dr_cust_name + dr_cust_lastname END FROM DOCTOR_MASTER WHERE DR_CUST_DOCTORCODE =@DOCCODE
Please suggest some ideas or any other idea please suggest Thank you.
I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.
In SQL Server Management Studio, this works fine:insert into table_3(column_a, column_b) select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b from table_1 left outer join table_2 on table_1.column_c = table_2.column_c
That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)
But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420 The column status returned was: "The value violated the integrity constraints for the column.".
And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.
So what is SSIS doing to screw up the results of the SELECT command???
I have been trying to get the following Selects to work using a case expression in the order by section.
I know I can easily separate out the two statements but I want to do a select using the case statement below ; however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.
If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.
Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.
Thanks J declare @date set @date = '2001'
select internalID from section_data_v3
union
select internalID from section_data_v4
order by case when (@date = '2001') then internalID end desc
Is there a method for converting the first character of a account name to uppercase and the the remaining characters to lower case? I've used the substring procedure but for a name like 'MY NEW COMPANY', how could I convert it to 'My New Company' ? Thanks, Terry
I have a case statement which I need to alter in my query. The field [sr.spec_value] contains either 1 or 0. When it is 1 I want the case expression to return 'YES' when 0 then 'NO'. I've tried a couple of options from the forum but am not getting it right. Here is the original case statement from my query:
case when sr.spec_value = 1 then 'YES' when 0 then 'NO' end [Fixed].
l'm running this procedure and l get this error. All l'm trying to do is to get the size of the database and its objects and what the size should be so that its sized right. Is there a better way of doing this ?
FROM sysobjects o, syscolumns c, sysindexes i WHERE o.id = c.id AND o.id = i.id AND (i.indid = 1 or i.indid = 0) AND o.type = 'U' GROUP BY o.name COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))
GO
(17 row(s) affected)
Server: Msg 8115, Level 16, State 2, Procedure sp_totalsize, Line 3 Arithmetic overflow error converting expression to data type int.
Under certain circumstances I am getting the following error
"Arithmetic overflow error converting expression to data type int"
when running the following code:
SELECT Count(*), Sum(GrossWinAmount) FROM LGSLog WHERE (CurrentDate >= '9/1/2004 8:00:00 AM') And (CurrentDate <= '9/27/2004 7:59:59 AM')
If I remove the "Sum(GrossWinAmount)" from the select, it works fine. I therefore believe that Sum is causing the error. Is there a version of Sum that works with larger variables, such as a BigInt? If not, is there some way to do the equivalent using larger numbers? I need to allow for the possibility of obtaining one month's summary, and sometimes the summary value is apparently too large for Sum to handle.
I've got this error message while generate the output with ASP:
"Microsoft OLE DB Provider for SQL Server (0x80004005) Arithmetic overflow error converting expression to data type int."
it indicate that the error is related to this line: "rc1.Movenext"
where rc1 is set as objconn.Execute(sql).
Not all outputs result like this, it happens when it has many relationships with other records, especially with those records which also have many relationships with other records.
Can anyone suggest a solution? I've tried to increase the size of the database file, but it doesn't work.
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber --------------------------------------------------------------- 1 Test 08/08/2012 Multiple 2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
