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
Hello, we've an Oracle transition in the pipeline and want to convertall our database objects to upper case. Any one got a script ortechnique (other than manual) to do it?Many thanks, Kevin.
Folks, what script must I use, as a part of CREATE TABLE, to automatically convert characters to UPPER case on insert? I wrote <CHECK (country = UPPER (country)> in the CREATE TABLE, which was wrong, because the values were still in the lower case. The sample script is:
CREATE TABLE address (street varchar(40), city varchar(20), state char (2), zip varchar (10), country varchar (20))
When a user types "Canada", I want the inserted value be "CANADA"
The all caps text strings at the beginning of the field need to end up in a separate field than the mixed strings, and the mixed strings need to stay together. The field length varies, as do the lengths of the all caps text strings. There are a lot of records, so I would be interested to know if there was a way to proceed without manually editing each line.
Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional. Thanks in advance for the help.
I have a problem. I need to rename all columns of a database to uppercase. Since SQL SERVER 2005 does not support changing system tables is there a smooth way to do this? Has anyone ideas for a script? point me to the right direction. I have found the stored procedure sp_rename which could be useful (or would it be better to alter the tables)... So any help would be appriciated very much...
Almost all of our character fields are stored in upper-case. Is there an easy way to force SQL Server char and varchar fields to upper-case? Something I can do in SQL Server instead of in the client? It needs to apply to any new records.
There are some exceptions (email addresses for one). I don't mind going through each field and changing something.
hi i want to select * from table1 where name =petter?now if there is many type of petter in table linke PETTER , Petter And petter which record will come in display?if i want all this three (PETTER,Petter,petter) will come in display which command is for this ??? regard
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
Our sql server 2005 database is receiving data from a third part program over which we have no control. We need to be able to automatically convert data entered in one column of one table to UPPER case only.
An IBM Global Services consultant is telling my client that in order to have SAP BI read any data from any other application supported by SQL Server 2005, that all tables and fields MUST be in UPPER CASE. This would mean that SAP BI could not read ANY data from AdventureWorks (which everyone needs ) but more importantly from 95% of applications written and stored on SQL Server. I find this to be ludicrious, frankly, but don't know how to find out if it is true. Anyone?
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].
I have a query where I need to see in 2 seperate columns, the Actual date of deliveries and collections, and the Invoice Week number of the same. The problem is that in our ERP system delivery date (di.ddate) is a date field, and the collection date is nvarchar. This is my query and sample results below, as you can see I'm getting nulls for the collection lines. How can I do this efficiently
select di.dticket, di.item, case di.item when 'DEL' then di.ddate when 'COL' then CONVERT(datetime,di.text2,103) end ActDate, Case when di.item = 'DEL' then (select YearWeek from MCSReports.RptRevenueGuideWeekLookup where di.ddate between FromDate and ToDate) end InvWeek
from deltickitem di where item in ('DEL','COL')
Sample results below:
dticket item ActDate InvWeek -------------------- -------------------------------------------------- ----------------------- -------- 01-000002 DEL 2011-07-05 15:53:00.000 2011-27 01-000002 COL NULL NULL 01-000004 DEL 2011-07-05 16:04:00.000 2011-27 01-000004 COL NULL NULL 01-000005 DEL 2011-07-05 16:08:00.000 2011-27 01-000005 COL NULL NULL 01-000006 DEL 2011-07-05 16:19:00.000 2011-27 01-000006 COL NULL NULL 01-000007 DEL 2011-07-05 16:24:00.000 2011-27 01-000007 COL NULL NULL 01-000008 DEL 2011-07-05 16:29:00.000 2011-27 01-000021 DEL 2011-07-05 08:48:00.000 2011-27
I have data in a SSIS package that I need to alter to something else.
The source column is a VARCHAR(3) column and it only contains two possible values, "ACT" or "CLS".
The destination column is a CHAR(1) column. Where the value of the source column is 'ACT' I want to put '1' in the destination and where the value of the source column is 'CLS' I want to put '0'.
I can do this easily in T-SQL using a CASE statement but the source data is an Ingres database and CASE isn't a valid SQL keyword.
Can I use a data conversion task to do this in SSIS? and if so, what's the syntax?
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 ~
------------------------------------------------------------------ Character Set = 2, cp850 Code Page 850 (Multilingual) character set. Sort Order = 50, bin_cp850 Binary ordering, for use with Code Page 850 (cp850). Characters, in Order
SELECT h1.ftraccode, CASE WHEN FTRAADDRED='A' then h1.ftrascode end as 'From Sec', CASE WHEN FTRAADDRED='r' then h1.ftrascode end as 'To Sec', case when ftraaddred ='A' then h1.ftradesc end as 'From Description', case when ftraaddred ='r' then h1.ftradesc end as 'to Description' from bHISfile h1 where h1.ftradesc like 'sw%' order by 1 ---------------------------------------------------------------- clintcode |from_sec | to_sec| from_desc | to_desc --------------------------------------------------------------- ABADJ16421 |NULL | MMTEI |NULL |SWITCH TO OAPIF ABADJ16421 |OAPIF | NULL |SWITCH FROM MMTEI |NULL
2(row)
Expected output like this
---------------------------------------------------------------- clintcode |from_sec | to_sec| from_desc | to_desc --------------------------------------------------------------- ABADJ16421 |OAPIF | MMTEI |SWITCH FROM MMTEI |SWITCH TO OAPIF
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1 WHEN PracticeCode IN (.......) THEN 1 WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1 ELSE 0 END
I need to put these conditions in config table and generate dynamic sql. What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1 WHEN PracticeCode IN (.......) THEN 1 WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1 ELSE 0 END
I need to put these conditions in config table and generate dynamic sql.
What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
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.
I was wondering what more experienced DBAs have observed with regard to the capacity of a MSSQL DB. Is there an upper threshold of rows where performance becomes unacceptable? I have a fairly slow, but constant input rate of approximately 2,000 rows every 60 seconds or so (that is a little high, but I'm interested in worse case scenario here). That is up 172,800 rows a day. (I'm being overly pessimistic here.) We'd like to be able to keep all of this around as long as possible.
Or would a more heavy duty DB be in order for these sorts of data rates?
Hi expert, I would like to ask regarding the UPPER function in SQL Query. I was tryin' to create a scipt that will give me a result of all the names that are in UPPER case format, but when I tried to execute the script the result is not right, it also retrieves all the records that are in PROPER case.
SQL Script: SELECT id, name FROM table_1 WHERE UPPER(name) LIKE 'DAR%'
@DeptID nvarchar(10) ) As If Exists ( Select DeptName From Departments Where DeptID LIKE @DeptID ) Return 1 Else Return 0
Now I want to apply replace and upper functions to DeptID in database before saying "DeptID LIKE @DeptID".
for example the parameter is :"D&V" DeptID in database is:"d & v" //there are spaces
if I say DeptID LIKE @DeptID nothing is found because of character nonmatching So I have to apply replace & upper functions to the column DeptID in database