Converting Field Values With ALL CAPS To Inital Caps???
Jun 18, 2004
I have found a stored proceedure that will convert a string from any format to initial caps followed by lower case letters. However, I'm such a newbie to SQL Server that I have no idea how to run the SP on a table.
Here is the stored proceedure: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=581&lngWId=5
How would I get this to run within a query to update a field?
For example, I have a field called 'name' with records like:
3M <- should remain '3M'
123 INTERIORS <--- should be '123 Interiors'
ALLIED SIGNAL <-- should be 'Allied Signal'
I have a table that I'd like to change some of the data. All of the employee information is entered in all CAPS. Is there a way to change JANE DOE, to Jane Doe?
Also, there are instances like this example "DOE, JANE".
Is there a performance hit if I use CAPITOL LETTERS (like this) in column names for my SQL Server tables? Logically I would think that lower case letters would have better performance because they are smaller characters and easier to store.
The states in this report are all in caps TEXAS, CALIFORNIA, etc.. Is there a way to use expressions to only have the first letter in uppercase and rest in lowercase?
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = 0,
bControlRiskIsAffirmed = 0,
bCombinedRiskIsAffirmed = 0,
bApproachTypeIsAffirmed = 0,
bCommentsIsAffirmed = 0
FROM EngagementAuditAreas WITH(NOLOCK) ...
And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).
UPDATE EngagementAuditAreas
SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,
numInherentRiskID = parent.numInherentRiskID,
numControlRiskID = parent.numControlRiskID,
numCombinedRiskID = parent.numCombinedRiskID,
numApproachTypeId = parent.numApproachTypeId,
bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),
bControlRiskIsAffirmed = (numControlRiskID IS NULL),
bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),
bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),
bCommentsIsAffirmed = (parent.txtComments IS NULL)
We made a poor decision a long time ago when designing our databasestructure. We used bigint data types as the identity keys for many ofour base tables. For many reasons I would like to change these fieldsto int at the largest. The largest data in these fields is around200,000. I know that int can easily store this.What should I be worried about when changing these fields from bigintto int? If anything. Your help is appreciated. I did severalsearches without much luck.
I have a table with an integer field (contains test values like 2, 7,8,9,12,..) that I want to convert to an Identity field. How can this be done in t-sql?
I have to convert values like 0x01A4 to integer and I just can not get it right!
Select Convert(int, 0x01A4) works OK but is not useful.
But Declare @hex varchar(20) Select Convert(int, @b) yields: Server: Msg 245, Level 16, State 1, Line 13 Syntax error converting the varchar value '0x01A4' to a column of data type int.
I did create the field on table as TinyInt. I created an appending query and appended the records to the SQL table. Now I have 0's or 255's in the field. Shouldn't they be 0's and 1's instead???? What am I doing wrong? What's the best way to convert the Yes/No fields into SQL, since I want to keep the access front end. Thanks for any help.
I have been asked to investigate the feasibility of converting an Invoice Number field from 6 to 7 digits, and allowing alpha characters. This means CHAR. The Invoice Number is not used outside of the application, but changes would have to be made to the database, and VB code that runs the app. Any ideas on how to proceed?
Any responses would be greatly appreciated! Thanks
I have a column that is frequently updated with an append query. I need to turn all of the incoming positive values into negative values (as well as convert all of the existing). I tried to multiply the column by -1 in the formula option in table design view but couldn't find the proper formula format.
How can I create a field in a view that takes a date field andconverts it to a month?For instance, if I have a field called "CreatedOn" with a date of'2/22/2007', how can I create another field called "Month" that willsay "February"? Is there a built-in SQL function that does this (likeusing CONVERT) or do I need to write my own?Thanks!Lisa
I have a table with over a million rows and one of the fields containsamounts of money in text format.What is the most efficient way of converting this field to a numberformat that I can sum on?Regards,Ciarán
Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error: Unable to modify table. ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric. Any suggestions?
Hi. I I'm importing a text file with lot's of decimal values with this format xx.xx. The problem is that my locale is Portugal and the points are being striped off and are not being considered as decimal separators (for example I have values like 0.04 and in the sql server database i see 4). I have tried to change the locale but i receive a message saying that the locale is not installed in my system.
We have inherited an appointments database that has a table tblAppointments
Within this table there are 2 fields, ApptFrom & ApptTo, these are the appointment start & finish times.
My problem is, the values in these fields are held as integers ranging from 0 - 288. I have worked out the scale for the integers, it is based on a 24hr clock with each segment representing 5mins
Eg: 0 = 0:00
12 = 1.00
24 = 2.00
36 = 3.00
€¦
...
288 = 24:00
What I need is a piece of T_SQL that will translate these into times that can be read by a normal user
So, say I have a record with an ApptFrom = 36 & ApptTo = 42, I need to be able to show these as ApptFrom = 3.00 & ApptTo = 3.30
I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)
My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.
The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50)) I would like to have it -> 11/29/00 (datetime)
I have a field that was text YESNONull. When I imported it into SQL I converted it to Boolean and had to go back and null out the ones that were supposed to be null. I am now trying to use the fields in my old reports and find that I have to convert the values to YesNo inside the query. This causes me to have to change almost every report. So I am wondering if there's a easier way to return YesNoNull or should I just keep the field as a 3 byte text?
Code: SELECT Jobs_Table.JobNum, [Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces, The_Big_One1.HT_Mail_STId, IIf(IsNull([The_Big_One1].[First_Scan_Date]),"",IIf([The_Big_One1].[STC_SCAN]=True,"Yes","No")) AS STC,
[Code] ....
or
Code: SELECT Jobs_Table.JobNum, [Jobs_Table].[Basic]+[jobs_Table].[FullService] AS MailPieces, The_Big_One1.HT_Mail_STId, THE_Big_One1.STC_SCAN,
I have a calendar parameter in one of my reports. The values of the calendar are of the form 'yyyy-mm-dd hh:mms'. And they are string values.
Now when I generate the report I have a textbox that takes the parameter label (e.g., parameter!calendar.label). However, I would like to format (or trim) the label as just 'yyyy-mm-dd'. Does anyone know how I can do this? I tried to change the parameter value format to datetime but this just throws an invalid datatype error...
As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.
I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.
Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.
It leads to massive implicit conversions and I am trying to change this.
To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.
Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.
I have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.
SELECT c.juris_id, b.jrnl_mo_yr FROM a_trueup a, #t_mths b, r_rj c   WHERE a.rlzd_mo_yr =* b.jrnl_mo_yr AND a.juris_id =* c.juris_id
[code]....
I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
