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".
I would like this to be "Doe, Jane" (Naturally).
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'
Hi,
Which is the best practice ?
1. Writing t-sql in All small (or)
2. Writing t-sql in All Caps
- thanks in advance
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.
View 4 Replies View RelatedThe 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?
View 3 Replies View RelatedHi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
View 1 Replies View RelatedI have data in the below format .
NameValuecategory
AAA510
BBB510
CCC510
DDD512
EEE512
FFF512
I want the result in the below format
NAMEValuecategory
AAA,BBB,CCC510
DDD,EEE,FFF5120
I have tried stuff but all six values(AAA...FFF) are coming in one row , however i need them as per the category.
I have data like this in a table
ID test_date Score
001 4/1/2014 80
001 5/4/2014 85
001 6/1/2014 82
I want to convert the data into a row like this:
ID test_date1 score1 test_date2 score2 test_date3 Score3
001 4/1/2014 80 5/4/2014 85 6/1/2014 82
How can I do that with T-SQL?
I need to create SQL to convert multiple rows data to single row for given subscriber#. Below is the example. In below example , I've 4 family members with same subscriber # and each members have separate rows, I want to combine member data for same subscriber in 1 row, so there would be a 1 row for each subscriber.
View 6 Replies View RelatedCould anybody tell me how to convert vertical data into horizontal data?I have a one-to-many relationship in sql server 2KProduct, ProductAccessory, one Product has many ProductAccessories.My Table design is like this:Table Product{ ProdId int, ProdNameId int, ....}Table ProductAccessory{ ProdId int, AccNameId int, AccUnitId int, ....}Because one Production has at most 4 ProductAccessoryI want to use a SELECT statement OR function to return ProdId, ProdNameId, AccNameId1, AccUnitId1, AccNameId2, AccUnitId2, AccNameId3, AccUnitId3, ....Any help will be appreciated! Thanks a lotJoseph
View 3 Replies View RelatedHello!
Can I convert character data (20000307070200)which is yyyymmddhhmmss to
smalldatetime and how?
Thank you very much.
Anny
Hello!
Please help me to convert datatype from one type to another.
Now I have date as yyyymmdd, I need convert it to yy/mm/dd.
Thank you.
Alona
Hi all,
I have some data that I want to pass to a proc using an XML parameter.
Data looks like this:
ElementName | ElementType
=================
Jim | Male
Henry | Male
Sarah | Female
I want the XML to look like this:
Code Block
<Elements>
<Element>
<ElementName>Jim</ElementName>
<ElementType>Male</ElementType>
</Element>
<Element>
<ElementName>Henry</ElementName>
<ElementType>Male</ElementType>
</Element>
<Element>
<ElementName>Sarah</ElementName>
<ElementType>Female</ElementType>
</Element>
</Elements>
Is that possible using T-SQL? Any help would be much apreciated.
Regards
Jamie
Hi to all :
Does anyone know how convert easily the SQL Records into XML?
Ocinx
I have a column - datatype 'money' and my price variable is (for instance)
8450
how do I put this value into the money column without getting this error?
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'db196009544.dbo196009544.vehicles', column 'price'. Use the CONVERT function to run this query.
ASP VBScript
I am using this syntax:
price = Upload.Form("price") '---this gives 8450---
SQL="UPDATE vehicles SET price='" & price & "' WHERE id=" & request.queryString("linkID") & ""
Thanks
mark
if i use the query
SELECT iditem, CONVERT(char(20), dt, 105) FROM planeamento
it works just fine.. but in this case the dt (date) field is not recognised..
SELECT planeamento.iditem, idmodelo, item_planeamento.idproduto, item_planeamento.idpele, cor, ordemfabrico, qtd, CONVERT(char(20), dt, 105) FROM produto INNER JOIN item_planeamento ON produto.idproduto = item_planeamento.idproduto INNER JOIN planeamento ON item_planeamento.iditem = planeamento.iditem WHERE planeamento.idfabrica = 51 AND alterado = 'FALSE' AND estado = NULL GROUP BY planeamento.iditem, produto.idModelo, item_planeamento.idProduto, item_planeamento.idPele, item_planeamento.cor, item_planeamento.ordemFabrico, item_planeamento.qtd, planeamento.dt
before, id just had "id" where "CONVERT(char(20), dt, 105)" is, but the date was printed in a unwanted format..
can you help me?
thanks a lot!
Hi members
I got the result set as shown below (By executing another query i got this).
Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7
But, I need the same result set as below
Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7
Can anyone provide me the tips/solution.
I am using vs2008 with c#.I have table in my database that has email addresses stored in a column. I would like somehow to take each email address from every row and paste them into a textbox that can be copied and pasted with a comma delimiter into outlook so I can send a mass email.I have no idea how I can accomplish this task.
View 3 Replies View RelatedHi,
I'm using the data type "money" in my SQL database and want to convert what's in txtPrice_textBox to the "money" format. I'm currently using the following code:
' objectCym.price = Convert.ToInt16(txtPrice_textBox.Text) '
Will this work? Is there any reason I should stay away from the "Money" data type?
Thanks,
David
Hi everyone,I have some data in a CSV file, and I have to import it into a table. For some reason, I am supposed to import this data into a temp table and then move it to the original table and I have to convert it to the right data types while I do this. Is there a better way to do this and how can I give custom error messages saying, for e.g., the data type cannot be converted, the right number of records are not present etc.
Thanks for the help.
Hello,
I am glad if someone help me. My question is like belo:
i have ascii data in one column name(failcode), i want to use it to link with other table which is the data type in integer. so i have to convert it into integer type.
May i know how to write in query to convert ascii to int in Sequence Server(Microsoft SQL Server 7.0)?
Hello,
I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?
Thanks all in advance,
Saurav
Hello All,
I have one table with 9 different columns for e-mail address like mail_address1, mail_address2 etc....
now i want to insert serial no 1 for mail_address1, serial no 2 for mail_address2 like wise for a particular id.
Current Format :-
code mail_address1 mail_address2 mail_address3
1111 abc@yahoo.com xyz@yahoo.com null
Required format:-
code sr_no mail_address
1111 1 abc@yahoo.com
1111 2 xyz@yahoo.com
1111 3 null
i hope that i am clear with my question.
Can any one help me?
Thanks
Prashant
Hello,
Table - 1 Format:-
Line_No Entity_Id Mid
1157530
2157540
3157550
4157560
5157570
6157580
7157590
81575100
Now i need the data into below Format :-
Line_NoEntity_Id Ticker Mid1Mid2 Mid3 Mid4 Mid5 Mid6 Mid7 Mid8
11575 COFP 30 40 50 60 70 80 90 100
Can any one help me out?
Thanks
Prashant Hirani
Hi
I am trying to output the data in the table to uppercase. I am using bcp to output as text file.Any possible solution to convert the data to uppercase from the table.Any tsql code for this
Thanks in advance
I have a string stored in a column of type image that I need to do string operations with in order to decode the containing infomation.
data [image] = 0x07FD0707FD0102F001000054004C005300410000000054004C00530041000000FF...
I can't cast or convert to nvarchar(max), varchar(max) as I always get an error. Explicit conversion from data type image to varchar(max) is not allowed.
I tried CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), DATA))
Which doesn't throw an error but seems to interprete the hexadecimal code to ascii characters and therefore is useless (ýýð...)
Hi, all my friends,
This is another rookie one.
Is there a way in SSIS dev environment that will let you convert/cast data type at desing time?
Under data flow mappings, there are so many things you can do, but why I did not find any easy way to convert?
I got this validation warning about not supportng converting type DT_STR to DT_I4, or something related.
Am I missing something obvious?
Thanks!
There must be a way to convert the binarydata field in the SQL trace outputto text data when the event type is a show plan. SQL Profiler does it buthow can it be done from an imported table?Danny
View 6 Replies View RelatedHow do I use the GetBlobdata to get the string representation?
I have tried numerous version of Encoders thinking that
System.Text.Encoding.GetEncoding(Row.TextData.ColumnInfo.CodePage).GetString(b) (where b is the byte array returned by GetBlobData)
But this doesn't work. I have had to resort to converting the field in SQL, so I don't have to deal with blobdata.
Hi, where I return the address details, in some of the fields they are blank, I need these blank fields as nulls, how do i do that ?
Thanks
Code Snippet
SELECT
a.[Account Name],
a.[Line Address 1],
a.[Line Address 2],
a.[Line Address 3],
a.[Line Address 4], b.*
FROM (
SELECT
abCUSA AS Company,
abCUSB AS Account,
0 AS DeliverTo,
abcnam AS [Account Name],
abcad1 AS [Line Address 1],
abcad2 AS [Line Address 2],
abcad3 AS [Line Address 3],
abcad4 AS [Line Address 4],
CONVERT(VARCHAR(20), abCUSA) + CAST(abCUSB AS VARCHAR(20)) AS [Customer Number]
FROM
LIVEAS400.S65C422B.WRFDTA.PARNADR
) a
Inner JOIN
(
SELECT
SLDATE as Date,
SLCUSA as [Company Number],
SLCUSB as [Customer Suffix],
SLDELN as [Deliber To Number],
SLTREF as [Trans Reference],
SLCNAM as [Deliver To Name],
CONVERT(VARCHAR(20), SLCUSA) + CAST(SLCUSB AS VARCHAR(20)) AS [Customer Number],
CONVERT(VARCHAR(20), SLCUSA) + CAST(SLCUSB AS VARCHAR(20)) + CAST(SLDELN AS VARCHAR(20)) AS [Full Deliver To Number],
-- SLTRT2 as [Trans type],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLGDSV * -1 ELSE SLGDSV END as [Goods Value],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLVATV * -1 ELSE SLVATV END as [VAT],
CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLTOTV * -1 ELSE SLTOTV END as Total,
SLDESC as [Trans Description]
FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR
) b ON a.[Customer Number] = b.[Customer Number]
I'm trying to import a set of database (.DBF) to the MS SQL Server 2005. But I found the Datatype for the Time column is in char format. Is it possible to convert it to Time format when importing to MS SQL?
Also, if there is a way to convert to TIME formant, Can I combine it with the Date colume?
I have a very big problem with a data conversion task. I have an input data source where some of the columns are optional (in terms of data). If any row does not contain a value, then it fails the data conversion task with error code -1071607681. (Could not convert due to potential data loss.) Why is the data conversion task incapable of handling empty values coming through?
View 6 Replies View RelatedHi! I need to insert character data into a SQL 2005 table in the datetime format. The strings I need to convert are in the following format: ddmmyyyy
I tried using case and convert:
cast('08162006' as datetime))
convert(datetime,'08162006',101))
Both attempts fail with the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Any advice is greatly appreciated. Thanks.