Converting Data Types

I need to be able to convert a numeric data type to a text data type.

I've tried the TO_CHAR and the DBCONVERT functions but I am returned an error stating that they are not recognized functions.

I'm using the Query Analyzer on a Windows 2000 SQL Server.

Any help would be appreciated.

TechRickYou can use the cast or convert functions.|||Thanks,

I was just reading up on the CAST function. However, I've not been able to make it work yet.

In correction to my original post, I need to take a MONEY data type and convert it to a TEXT data type. I hope this possible.

Thanks again,

TechRick|||Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles|||You can also do it this way:

select cast(cast(price as varchar(30)) as text) from titles

But since the conversion is implicit between varchar and text you don't need to do this.|||Thanks again for the help. Your suggestion works great but when I try to update the data I get this:

Server: Msg 260, Level 16, State 1, Line 31
Disallowed implicit conversion from data type varchar to data type money, table..., column 'PRICE'. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 31
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

I'll let you know once I get it worked out.

Best Regards,
TechRick|||The way the error is reading is that you are trying to put a varchar into a money column - is that correct ?|||Yes, I have a large list of items collected from various tables and there are prices associated with these items. A good majority of these items have no price (.0000) and I would like to exchange the .0000 price for a 'CALL' or something similar.

I could not update the records with a "text" substitute so I thought I would convert the data type to TEXT so I can plug in whatever I want.

Thanks again,
TechRick|||Why did you choose text over varchar ? Could you post your update statement as well as the definition of the table you are updating ? You have to explicitly convert varchar to money and vice-versa using either cast or convert. But I am a little confused about the first error you received - why are you trying to put a varchar into a money column ?|||You'll have to excuse me, I've only been working with SQL for less than 3 weeks. I've been learning as I go (with my Dummies and Sam's books and this forum). I've already written a few complex queries and as for this particular situation, this is the last obsticle I need to overcome to put this query to rest.

Based on the error I was getting I assumed that I was doing something wrong but I didn't have the time to completely research it. I'll pick it back up on Monday. I think I need to understand the convert and cast functions better before I can make use of them. I'll be working towards that end.

As for now, I'm away from work and don't have easy access to the code. I'll post it Monday after I tinker a little more.

Thanks for the help.

TechRick|||Ah, by "convert" you mean that you would like to change the data type of a column from money to varchar so that you can store a mix of data in a single column. The easy answer is to just change the data type of the column in Enterprise Manager. If SQL Server can find a reasonable way to preserve the data, it will. Thereafter you can store any character data in the column, e.g. "Call" or "Operators are standing by!".

The "correct" answer is rather different, and a valid subject for debate. If you do not have a price for an item, the correct representation in the database should be different from a free item. For example, you might use the value NULL to indicate "call for price" and $0.00 for a free item. Alternatively, it may make more sense in your application to have a separate means of flagging items for which you don't want to publish a price, qualify for free shipping, have quantity discounts, ... . More columns or tables may be needed.|||Agreed about the 'correct' answer. Fortunately and unfortunately, I didn't write the application so I'm working with what's there.

I managed to get it taken care of by adding the convert on my select statment.

SELECT UPPER(ITEM), DESCRIPT, Q_STK, QTY_RESERVE, CAST (SELLPRICE AS VARCHAR(15)),...

By doing it this way I am able to manipulate the data any way I want.
Add 'CALL', 'FREE', etc.

Thanks all for the help. One last item and this query is behind me.

Best Regards,
TechRick|||Originally posted by rnealejr
Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles

rnealejr, I noticed as I was going back through all these posts that you were right on the money a long time ago! Thanks for the help. I think I was trying to perform an update on the column using the cast rather than taking the data in converted from the start. Anyhow, just wanted to thank you for your help. Too bad I had to learn the hard way.

Best Regards,
TechRick|||Thanks for the email and compliment as well as a good pun (right on the money) - I enjoyed that.

Good luck.|||Another handy tool for fudging return values within a query is throwing in a CASE, e.g.:

select Description, ServingSize, case when Price<>0.0 then Convert(VarChar,Price) else 'Call' end as 'AdvertisedPrice'
from PiecesParts where Fused=1

Note that there are two slightly different versions of CASE. One lets you test a single expression against multiple values, while the other lets you test multiple expressions.

You can swindle a lot of logic into a CASE or nested CASEs. For example, it could check for quantity price breaks or apply discounts based on data from other tables or variables. The result is just another (computed) column in the recordset returned from the query. (As such, it isn't writable.)

convert varchar to decimal

When running this query, several records are returned but then sql server gives the follwing error. Can you see how it can be solved please? Thanks

select
case
when isnumeric([Column 9]) = 1 then convert(decimal(24, 4), [Column 9])
else
null
end
from
tblCEMTradeFeed

Error:

Error converting data type varchar to numeric.

You can not trust function ISNUMERIC a 100%. This function returns 1 also for values like '.', '2E3' (scientific notation), '+', '-', but not all of them can be conevrted to numeric data type.

select cast('2E3' as float)

go

select cast('2E3' as numeric(5, 2))

go

What is wrong with IsNumeric()?

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

AMB

|||How do I fix the sql then please?|||

The following expression might fix a issue,

Note:

Is numeric function will return true for “.”, “$” & etc.

Code Snippet

case when

isnumeric([Column 9]) = 1

and patindex('%[0-9]%',[column 9]) <> 0

then convert(decimal(24, 4), [Column 9])

else

null

end

|||

Hi Manivannan.D.Sekaran,

I guess the OP is expecting a decimal separator in the data, if not, why to convert to numeric(24, 4).

I think that the expression:

> and patindex('%[0-9]%',[column 9]) <> 0

will not yield the expected result. The following value will cause an error '2E3'.

select

case

when isnumeric([Column 9]) = 1 and patindex('%[0-9]%',[Column 9]) <> 0 then convert(decimal(24, 4), [Column 9])

else null end

from

(select '2E3' as [Column 9]) as t

go

AMB

|||

Did you check the link I attached to the post?

AMB

|||

Hi HUNCHBACK (padern me i can't able to get your orginal name),

Yes I agree with you. I hope STR should be a right choice to convert the string to decimal

Code Snippet

select

[Column 9],

Case When

patindex('%[0-9]%',[Column 9])<>0

Then

Case When

Isnumeric(str(replace([Column 9],'$',''),24,4)) =1

Then Cast(str(replace([Column 9],'$',''),24,4) as Numeric(24,4))

End

End

from

(

select '2E3' as [Column 9]

Union All

select '2E24' as [Column 9]

Union All

select '2E80' as [Column 9]

Union All

select '100' as [Column 9]

Union All

select '.' as [Column 9]

Union All

select '$' as [Column 9]

Union All

select '$9' as [Column 9]

Union All

select '878.8373738' as [Column 9]

Union All

Select 'mani'

) as t

|||

Hi Manivannan.D.Sekaran,

Much better. Try adding:

(

...

union all

select '$9'

) as t

AMB

Convert to Number IF it is a number

Pardon my newbieness:
I have a column that could contain numbers or text (not my idea). All
of the values are returned as text. I want to return the value as a
number if it is a number, otherwise (of course) leave it as text. What
can I do?Return the value to where?
A single column in a resultset has exactly one datatype.
Can't your receiving application or front end code figure out what kind of
data it is?
Why are you storing numbers and strings in the same column?
A
"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>|||> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
Use the ISNUMERIC function. Check the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>
A column only has a single datatype so I'm going to assume you require a
result set with two columns: one VARCHAR, one INTEGER. I will also assume
that when you say "text" you really mean "VARCHAR". Doing this using the
TEXT datatype would be a bit more tricky. That's too many assumptions but if
you don't post a more precise spec then assumptions and guesses are what you
will get. You can help us to help you better by taking the advice in the
following article:
http://www.aspfaq.com/etiquette.asp?id=5006
Here's my example:
CREATE TABLE tbl (x VARCHAR(10) PRIMARY KEY) ;
INSERT INTO tbl (x)
SELECT '123' UNION ALL
SELECT '99A' UNION ALL
SELECT 'ABC' ;
SELECT x, CAST(num AS INTEGER) AS num
FROM
(SELECT x,
CASE WHEN x NOT LIKE '%[^0-9]%' THEN x END AS num
FROM tbl) AS T ;
Result:
x num
-- --
123 123
99A NULL
ABC NULL
(3 row(s) affected)
Hope this helps.
David Portas
SQL Server MVP
--|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23R%23NQQKEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Use the ISNUMERIC function. Check the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
Before using ISNUMERIC Tod should be aware of its limitations. Read:
http://www.aspfaq.com/show.asp?id=2390
David Portas
SQL Server MVP
--|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.

convert stored procedures returned output to varchar from int?

I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(

Syntax error converting the varchar value 'ABC' to a column of data type int.

SQL file

======================

DECLARE @.S AS VARCHAR(1000)

EXEC @.S = PDT.DBO.SP_RPT
'ABC'

SELECT CONTENT = @.S -- this is the value I am trying to pass as content of the email

EXEC PRODUCTION.DBO.SENDEMAIL'xxx@.hotmail.com', 'Notification', @.S

======================

Stored Procedure

======================

CREATE procedure sp_RPT
( @.array varchar(1000) )
AS

DECLARE @.content AS VARCHAR(2000)

SET @.content = 'RPT: ' + @.array + ' loaded successfully '

SET @.array = 'ABC'
RETURN CONVERT(VARCHAR(1000),@.array)

GO

try

cast( @.array as varchar(1000))

Hope this help

|||Don't use RETURN values in that manner. Either use an output parameter, or a resultset. RETURN values should be used as a control channel, not as a data channel; RETURN's purpose is to terminate control of a stored procedure immediately and also to report on how control was terminated (0 = normal/good, anything else is abnormal/bad).

Convert sql proc to a c# class?

Anyone have code to convert a sql proc to a C# class?

Specifially something to create the columns returned from the proc.

Thanks.I figured this out myself.

Just execute the proc in a sqldataadapter, fill a dataset, then loop thru the columns
while creating text output for a C# class.

Pretty simple.|||Are you referring to a code gen script? Can you elaborate on your request?

Convert returned values

I have a query where one of the fields returns a 0 or 1. I'd like the
zero's to show on the report as a "No" and the one's be a "Yes". Does
anyone know how to do this?
Thanks,
ColinIn Expression Editor
=IIF(Fields!ColumnName=1,"Yes","No")
--
THANKS & PLEASE RATE THE POSTING.
--RAVI--
"Colin" wrote:
> I have a query where one of the fields returns a 0 or 1. I'd like the
> zero's to show on the report as a "No" and the one's be a "Yes". Does
> anyone know how to do this?
> Thanks,
> Colin
>
>

Convert returned value to Integer

I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.

----------------
DECLARE @.TransactionID int
<-- INSERT statement (after inserting the record, select the identity ID) --->
Select @.TransactionID = @.@.Identity
RETURN
----------------

.
.
.
Dim transactionID As Integer
connection.Open()
Dim reader As SqlDataReader
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim p3 As New SqlParameter("@.TransactionID", SqlDbType.Int)
p3.Direction = ParameterDirection.Output
transactionID = Convert.ToInt16(p3.Value)
connection.Close()
.
.
.

I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.

I tried many different ways of conversion but couldn't resolve the error. Could anyone help?

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

|||

ndinakar:

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?

You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.

check ifthis articlehelps.

hmm.. pardon me as this is my first time with asp.net

In the article:

myCommand.Parameters.Add(New SqlParameter("@.ProductId",SqlDbType.Int))

myCommand.Parameters.Direction = ParameterDirection.Output

The line in red doesn't work. I guess I'm using asp.net 2.0, that's why... so I changed to something like

command.Parameters("@.ProductID").Direction = ParameterDirection.Output

Yupz.. I'm retrieving through output parameters, I tried ExecuteReader but still couldn't work.

Actually I have these two tables for a shopping cart project - OrderTransaction and OrderDetails

OrderTransaction stores general information of TransactionID, Status, ShippingAddress etc of an order. OrderDetails stores details of a particular transaction which has foreign keys references to the Ids of OrderTransaction, Customers and Products table.
Hence, the reason for retrieving the TransactionID is so that I can create records for the items in the shopping cart in the OrderDetails for the particular transaction.

Code:

Dim transactionID As Integer
Dim connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
Dim command As SqlCommand = New SqlCommand("AddOrders", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@.CustomerID", Context.Session("myID"))
command.Parameters.AddWithValue("@.DateCreated", DateTime.Now.ToString())
command.Parameters.AddWithValue("@.Verified", 0)
command.Parameters.AddWithValue("@.Cancelled", 0)
command.Parameters.AddWithValue("@.CustomerName", nameTB.Text)
command.Parameters.AddWithValue("@.ShippingAddress", shippingaddressTB.Text)
command.Parameters.AddWithValue("@.CustomerEmail", emailTB.Text)
command.Parameters.Add("@.TransactionID", SqlDbType.SmallInt)
command.Parameters("@.TransactionID").Direction = ParameterDirection.Output
connection.Open()
command.ExecuteNonQuery()
transactionID = Convert.ToInt16(command.Parameters("@.TransactionID").Value)
connection.Close()

Stored Procedure:

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50)
)

AS

DECLARE @.TransactionID int

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated), @.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)

Select @.TransactionID = @.@.Identity

RETURN

The error is
"Procedure or function AddOrders has too many arguments specified."
Line 161: command.ExecuteNonQuery()

I think that's because I did something wrong in the lines ingreen.Appreciate if anyone helps. Thanks in advanced.
|||(1) The error is exactly what it says. You are trying to add @.TransactionId as a parameter when you havent declared it as a parameter in your stored proc. modify your stored proc as follows:
(2) SCOPE_IDENTITY() is more efficient than @.@.IDENTITY. Check out books on line for more explanation.
(3) Since the value you are returning is through OUTPUT parameters, use ExecuteNonQuery.

ALTER PROCEDURE AddOrders
(
@.CustomerID int,
@.DateCreated smalldatetime,
@.Verified bit,
@.Cancelled bit,
@.CustomerName varchar(50),
@.ShippingAddress varchar(200),
@.CustomerEmail varchar(50),
@.TransactionId INT OUTPUT
)

AS

BEGIN
SET NOCOUNT ON

INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
VALUES (@.CustomerID, Convert(smalldatetime,@.DateCreated),@.Verified, @.Cancelled, @.CustomerName, @.ShippingAddress, @.CustomerEmail)
SELECT @.TransactionID= SCOPE_IDENTITY()

SET NOCOUNT OFF
END