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
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"
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%'
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.
@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
My SQL Server database is not case sensetive. How can I compare like cluase with search for capital and small letter? For example SELECT add1 from xcty_all where add1 like '%AL'%' I need only ................... 10 ltncewwod way AL 456 Ruio St. AL NOT
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.
There may/may not be an upper limit for the number of rows in a table, but is there any performance-related limit?
I'm designing a database that stores results that have been acquired from a number of devices. Each device provides a set of data measurements every 10 minutes. Therefore each year a device will produce 52000 sets of results. If I design a table to store a row for each set of measurements from a device (PK is based on the timestamp and the deviceID), and if there are 100 devices recording for 5 years, there will be 52000x100x5 rows. Would I get a performance increase by separating this data into one table per year? Perhaps the year could be appended to the table name to identify the particular tables.
A secondary issue is some devices can also be configured to produce a different set of measurements every 10 seconds. In this case there will be hundreds of millions of rows over a 5 year period. Therefore I am considering bulking the results into an array for a 10 minute period, and storing this array as a blob each 10 minutes. Is this going to be faster or slower than having hundreds of millions of rows?
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.
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 There, I have a column which contain alphanumeric values: aab123add234cdf423dej553edg543 If I try to return records between these values 'a' and 'e' it will only go as far as d. (first letter) aab123add234cdf423dej553 This is true if I use where value between a and e Or if I use greater than equal to operators Any help would be great. Thanks Stuart
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
I have several tables in a deployed database in which the primary key is of type int, and autoincrements by 1 each time a record is added. My question is, since ints are 32-bit, what happens when its value reaches 4,294,967,296? I know that seems like an extrememly large amount of records, but when we imported the data into the database it started at key value 1,000,000. I don't know how to make it use lower numbers which are currently not being used (numbers below 1,000,000), and I am worried I will have problems when I reach the upper bound. What kind of problems could this cause? Should I change the primary key's type?Thanks!
One of our database is approaching the gigabyte size. I know that microsoft claims to support terabyte databases with sql server 7.0. I was wondering if anyone could tell me about the max size of database they have used on an OLTP site without running into problems. ofcourse with SQL Server.
Hi, all:I'd heard that the upper row limit in SQL of 6080 bytes may have beenincreased with SP3.Can anyone confirm/deny this? Is this still a 'carved-in-stone' uppercap?Thanks,DW.
In the database, most of our cities are stored in all upper case. For reporting purposes, I need to have them returned as upper/lower. I used the below function, which works great for one word cities. However I can’t figure out how to get it to capitalize the 1st letter of each word, for addresses containing multiple names such as Rancho Santa Margarita.
I'm still haven't resolved the issue with displaying information from a SQL database. The text I'm displaying is in ALL CAPS in the SQL database, and I'm trying to convert it so that when I display it in gridview, The First Letter Of Each Word Is Capitalized, as apposed to ALL CAPS. I've tried the text-transform feature of CSS, but I noticed in a SQL book there are LOWER() & UPPER() string functions. The ideal thing to do then, would be to do some select statement that converts all the incoming text to lowercase, then use the CSS text-transform: capitalize , to convert the first letter of each word to caps. Basically, I need a select statement or something that converts my sql material to lowercase. Thanks.
I want to search for alphanumeric values between an upper and lower bound in a sql database.For example: search for a serial number like pvf-456-3b. Upper bound is q, lower bound is g.I should then get every serial number starting with g - q.If possible the bounds should be more specific like "search for serial number between gt2 and qy"Can anybody help me out?
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.
Our development team wanted to create a database user for each application user in the application and use these for granular data access control, which at first, sounded like a good idea but our initial testing ran into some interesting results.
Our target user base was about 15 million users with an estimated 1% concurrency rate, and finding no MS documentation on an upper limit to the number of users a database can have we began some load testing to see how the database performed. In the hundreds of thousands of users range our test database had a hard time performing well under light loads (even without any concurrent connections).
When we purged the users and reverted back to just a handful of service accounts, performance went back to "normal" under the same loads. I began to wonder if this is a situation where throwing more hardware at the problem would overcome the issue or if there is a practical upper limit to the number of users a single database can handle well.
(There were of course other cons to this arrangement and I certainly was never going to expand the users tree in the object explorer for a database like this, but we thought it a solution worth investigating.)
What is the largest number of users any of you have had in a single database?
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?