Using CONVERT function to convert NUMERIC to VARCHAR

I am creating a trigger on a table where 2 columns are
defined as numeric. This trigger captures the data and
inserts it into another table where I want to combine the
numeric data with a statement. I've tried using the CONVERT
function, but it is giving me a mis-matched token error.
I've looked through the documentation online, but it is not
really helping me or I'm not understanding correctly. I've
pasted a snippet of what I'm trying to do without using the
CONVERT function.

**user_level and region_id are the numeric values**

select  @whatchanged =
case    when i.user_level = d.user_level and i.region_id =
d.region_id
        then 'No change has been made'
        when i.user_level != d.user_level and i.region_id =
d.region_id
        then 'The User Level has been changed from ' 
+d.user_level+ ' to ' +i.user_level
0
Andrew
1/4/2007 3:51:11 PM
sybase.ase.general 8655 articles. 0 followers. Follow

9 Replies
8023 Views

Similar Articles

[PageSpeed] 49

You're trying to concatenate a numeric into the middle of a
string.  Won't work.  What you need to do with the last part
of the case statement is this...

         then 'The User Level has been changed from ' +
convert(varchar(9), d.user_level) + ' to ' +
convert(varchar(9), i.user_level)
0
Don
1/4/2007 3:55:14 PM
That is what I thought to do but when I put that function
in, it gives me an error that convert is an unexpected
function.

> You're trying to concatenate a numeric into the middle of
> a string.  Won't work.  What you need to do with the last
> part of the case statement is this...
>
>          then 'The User Level has been changed from ' +
> convert(varchar(9), d.user_level) + ' to ' +
> convert(varchar(9), i.user_level)
0
Andrew
1/4/2007 4:17:00 PM
Perhaps you should post that actual code and the error message.

However, I'd generally recommend using the STR() function instead of
CONVERT() for this purpose.

-bret


Andrew, T. wrote:

> That is what I thought to do but when I put that function
> in, it gives me an error that convert is an unexpected
> function.
>
> > You're trying to concatenate a numeric into the middle of
> > a string.  Won't work.  What you need to do with the last
> > part of the case statement is this...
> >
> >          then 'The User Level has been changed from ' +
> > convert(varchar(9), d.user_level) + ' to ' +
> > convert(varchar(9), i.user_level)

0
Bret
1/4/2007 4:28:01 PM
***Actual Code****

case    when i.user_level = d.user_level and i.region_id =
d.region_id
        then 'No change has been made'
        when i.user_level != d.user_level and i.region_id =
d.region_id
        then 'Only the User Level has been changed from '
convert(varchar(9), d.user_level) + ' to ' +
convert(varchar(9), i.user_level)

**Actual ERROR**

unexpected token: convert

> Perhaps you should post that actual code and the error
> message.
>
> However, I'd generally recommend using the STR() function
> instead of CONVERT() for this purpose.
>
> -bret
>
>
> Andrew, T. wrote:
>
> > That is what I thought to do but when I put that
> > function in, it gives me an error that convert is an
> > unexpected function.
> >
> > > You're trying to concatenate a numeric into the middle
> > > of a string.  Won't work.  What you need to do with
> > > the last part of the case statement is this...
> > >
> > >          then 'The User Level has been changed from '
> > > + convert(varchar(9), d.user_level) + ' to ' +
> > > convert(varchar(9), i.user_level)
>
0
Andrew
1/4/2007 4:43:29 PM
Actually I prefer convert() since the result string will not be padded with 
spaces when using convert(varchar, <expression>).
Anyway I agree that without the error msg there's no answering this 
question.

Rob V.


"Bret Halford" <bret@sybase.com> wrote in message 
news:459D366D.B6A09EF7@sybase.com...
> Perhaps you should post that actual code and the error message.
>
> However, I'd generally recommend using the STR() function instead of
> CONVERT() for this purpose.
>
> -bret
>
>
> Andrew, T. wrote:
>
>> That is what I thought to do but when I put that function
>> in, it gives me an error that convert is an unexpected
>> function.
>>
>> > You're trying to concatenate a numeric into the middle of
>> > a string.  Won't work.  What you need to do with the last
>> > part of the case statement is this...
>> >
>> >          then 'The User Level has been changed from ' +
>> > convert(varchar(9), d.user_level) + ' to ' +
>> > convert(varchar(9), i.user_level)
> 


0
Rob
1/4/2007 4:47:12 PM
I agree with Rob, as str() with a number biger than
100000000, does not work properly, while convert works OK.

Tartampion
> Actually I prefer convert() since the result string will
> not be padded with  spaces when using convert(varchar,
> <expression>). Anyway I agree that without the error msg
> there's no answering this  question.
>
> Rob V.
>
>
> "Bret Halford" <bret@sybase.com> wrote in message
> news:459D366D.B6A09EF7@sybase.com...
> > Perhaps you should post that actual code and the error
> message. >
> > However, I'd generally recommend using the STR()
> > function instead of CONVERT() for this purpose.
> >
> > -bret
> >
> >
> > Andrew, T. wrote:
> >
> >> That is what I thought to do but when I put that
> function >> in, it gives me an error that convert is an
> unexpected >> function.
> >>
> >> > You're trying to concatenate a numeric into the
> middle of >> > a string.  Won't work.  What you need to do
> with the last >> > part of the case statement is this...
> >> >
> >> >          then 'The User Level has been changed from '
> + >> > convert(varchar(9), d.user_level) + ' to ' +
> >> > convert(varchar(9), i.user_level)
> >
>
>
0
tartampion
1/4/2007 5:46:05 PM
You need add a '+' between the following two lines:

>        then 'Only the User Level has been changed from '
> convert(varchar(9), d.user_level) + ' to ' +

Rob V.



<Andrew T.> wrote in message news:459d3cc1.5203.1681692777@sybase.com...
> ***Actual Code****
>
> case    when i.user_level = d.user_level and i.region_id =
> d.region_id
>        then 'No change has been made'
>        when i.user_level != d.user_level and i.region_id =
> d.region_id
>        then 'Only the User Level has been changed from '
> convert(varchar(9), d.user_level) + ' to ' +
> convert(varchar(9), i.user_level)
>
> **Actual ERROR**
>
> unexpected token: convert
>


0
Rob
1/4/2007 6:08:08 PM
<tartampion> wrote in message news:459d4b6d.5317.1681692777@sybase.com...
>I agree with Rob, as str() with a number biger than
> 100000000, does not work properly, while convert works OK.
>
> Tartampion

(SNIP)

Are you referring to "str (approx_numeric)" or "str (approx_numeric, 
length)"?  (The default length value is 10.)  If a length greater than 10 is 
used, HOW does it not work properly? 


0
Carl
1/5/2007 11:43:45 AM
>         then 'Only the User Level has been changed from '
> convert(varchar(9), d.user_level) + ' to ' +
> convert(varchar(9), i.user_level)

You're missing the + before the first convert.
0
Don
1/5/2007 9:43:19 PM
Reply:

Similar Artilces:

Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.
Hello Guys,Have been getting this error( Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query. ) when running on the live environment but it was fine when run locally. If anyone has similar problem please let me know the fix you have done. Thank you.~ Remember To Mark The Posts Which Helped You As The ANSWER ~  Seems like your database schema differs between live environment and local. Check the definition of the table, and also what type of parameter you use in your code.If this post was useful to you, please mark it as ...

CONVERT function use
I'll go right to the point: I have a textbox in wich the user can seize a "money" value. But when i'm doing the INSERT command, it fails and says I cannot implicitely convert nvarchar data (mytextbox.text) to money data. I don't know how to handle this... objCmd = New SqlCommand("INSERT INTO tbl_appel_service_pieces " & _ "(fld_nom_piece, fld_quantite, fld_prix, fld_description, fld_num_appel) " & _ "VALUES (@fld_nom_piece, @fld_quantite, @fld_prix, @fld_description, @fld_num_appel)", objConn) objCmd.Parameters.Add(&qu...

Convert numeric to varchar
Hi, does anybody know how I can convert a number to a character datatype so that leading zero's will be in the result. E.g.: 56 -> '0056' TIA. Regards, Jan Hello, I believe that the easiest way is to just add zeros to the string. Example) SELECT "0000000000" + CONVERT(VARCHAR(10), @VALUE) You could also you replicate, stuff and other various system functions to perform this, but I believe the above is the easiest. /* Good for positive numbers only. */ 1> select field1, "Positives Only" = 2> right( replicate(&quo...

Format function (convert ) for numeric
set similar function of powerbuilder string for edit (formatting) numeric data ex: string(123444.18,'#,##0") = 123,444 or string(-123444.18,'($#,##0)") = ($123,444) thanks ...

error converting varchar to numeric
i have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion.... thanks***********************Dinakar NethiLife is short. Enjoy it.*********************** Never tried it myself but it should let you step through a sproc like you would your C#/VB.Net code behind. Walkthrough: Debuggin...

how can i convert varchar to numeric
i am sending string like(1,2,3) by VB and want this string to used with In operator.but my problem is this that when i am using this in my sql select statement error occurs b'coz the table field by which i am quering is numeric. -- Posted via http://dbforums.com On 13 Oct 2003 14:37:14 -0700, in sybase.public.ase.general pragya <member41060@dbforums.com> wrote: >i am sending string like(1,2,3) by VB and want this string to used with >In operator.but my problem is this that when i am using this in my sql >select statement error occurs b'coz the table f...

superreview granted: [Bug 409331] convert sites that QueryInterface to kBlockCID to use nsLayoutUtils:: GetAsBlock : [Attachment 295022] convert sites that QueryInterface to kBlockCID to use nsLayoutU
Robert O'Callahan (:roc) <roc@ocallahan.org> has granted superreview: Bug 409331: convert sites that QueryInterface to kBlockCID to use nsLayoutUtils::GetAsBlock https://bugzilla.mozilla.org/show_bug.cgi?id=409331 Attachment 295022: convert sites that QueryInterface to kBlockCID to use nsLayoutUtils::GetAsBlock https://bugzilla.mozilla.org/attachment.cgi?id=295022&action=edit ------- Additional Comments from Robert O'Callahan (:roc) <roc@ocallahan.org> cool. We probably don't need to take this for FF3 though. Let's land it post-FF3. ...

problem in using the convert function for date
i have a problem in sybase. select convert(char(10), dbo.classes.start_date, 101) from dbo.classes where dbo.classes.class_id = 316600 AND convert(char(10), dbo.classes.start_date, 101) >= '07/01/2001' ; i have a field start_date in the classes table.the field start date is a datetime field. i wanted to select the dates > '07/01/2001' from the classes table.there is a record in the classes table with the start_date value as 7/2/99 00:00:00 when i run the above query it gives me the record with 7/2/199 value also which i dont want.can you check is there...

Using RIGHT & CONVERT functions
Does anyone know why the following statement produces different results on System 10 vs. ASE 11.5.1. select right("00000" + convert(char(5), order_qty), 5) 10.0.3, NT 3.51 generates leading zeroes. 11.5.1.7, NT 4.0 SP 3 does not generate leading zeroes. However, using varchar produces leading zeroes on both systems. Hello Kevin, Sybase changes the behaviour of handling trailing blanks from 11.0.3 to 11.5.x. This change causes the problem. Try select right("00000" + convert(varchar(5), order_qty), 5) Bye Wolfgang Kunk Kevin Kopec wrote: &g...

Convert OST file into PST by using OST to PST Converter Software
To convert OST to PST format you can try this OST to PST converter software. It is good to use and easy to operate by perfect OST file recovery of OST database with migrate OST file into Outlook. Microsoft OST to PST software works on all OST file versions and gives you a safe conversion OST to PST. Through this application users not recover only emails but also all extract data from OST file into PST file with whole OST folders like: inbox, outbox, journals, remainders and appointments etc. It has simple and perfect way to recover of OST file also rip large sized PST file into small PST fil...

how to convert dynamicaly loading images to pdf using HTML to PDF Converter for .NET
how to convert dynamicaly loading images to pdf using HTML to PDF Converter for .NET Now am converting using how to convert dynamicaly loading images to pdf using HTML to PDF Converter for .NET  tool. But the dynamicaly loading images are not showing in the pdf .     Are you using 3rd party tool or you are using iTextSharp free utility? If it is 3rd party tool, it's always suggested to ask the company which built the tool. Sreedharhttp://www.w3coder.orgweblog http://weblogs.asp.net/skoganti Hi vishnusekhar, As to convert images/douments to pdf files in asp.net , have you read this article? http://www.colorpilot.com/pdfsample_aspnet.html It's a Step by Step tutorial, very clear to follow, so take a try. This may also help since the project source code is shared and attached: http://www.codeproject.com/aspnet/HTML2PDF.asp I hope my suggestion can help  This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving...

Convert OST file into PST by using OST to PST Converter Software [Edit]
This message is no longer available. ...

Convert function failing under ASE 12.0
ASE 12.0 (with one-off 9400): C:\Sybase\sample\ctlib>isql -U sa -P ##### -S ASE12_0SERVER 1> use ase12db 2> go 1> cnv_chardate_to_int '08/05/1998', 101, 0 2> go Msg 249, Level 16, State 1: Procedure 'cnv_chardate_to_int', Line 46: Syntax error during explicit conversion of CHAR value '08/05/1998 ' to a DATETIME field. (return status = -6) 1> quit ASE 11.5.1 C:\Sybase\sample\ctlib>isql -U sa -P ##### -S ASE11_5_SERVER 1> use ase1151db 2> go 1> cnv_chardate_to_int '08/05/1998', 101, 0 2> go l_date_int l_date_cha...

Not able to use sql Convert function in gridview
 Hi!Im trying to get europe time in one of my gridviews (instead of am/pm format), and tryed using following select-string:SelectCommand="SELECT Convert(DateTime, vagt.start, 13), vagt.***, medarbejder.fornavn + ' ' + medarbejder.efternavn AS Expr1, medarbejder.medarbejder_id, vagt.vagt_id FROM vagt INNER JOIN medarbejder ON vagt.medarbejder_id = medarbejder.medarbejder_id WHERE vagt.dags_id = @intDagsid" The thing to note ofc, is the Convert() part. Now, when i use that method on the "vagt.start" column, it gives me an error stating it cant fint a field...

how i convert varchar sal field to numeric in query
how i convert varchar sal field to numeric in query select sum(sal) from emp1 error:the sum or average aggregate operation cannot take a varchar data type as an argument. Use Cast as in: select sum(cast(sal as float)) from emp1...

Error converting data type varchar to numeric.
following is the error I am getting. Exception Details: System.Data.SqlClient.SqlException: Error converting data type varchar to numeric.Source Error: Line 107: " select '" & eventnumber & "','" & username & "','" & requestor & "', '" & bcode & "', max([Loan_ID]) from loan "Line 108: Dim myCommand1 As New SqlCommand(sql1, myConnection)Line 109: myCommand1.ExecuteNonQuery()Line 110:Line 111: Dim sql2 As String = " update Equipment set [Available_for_Loan] = 0 where barcode = '" & bcode & "'"Source File: C:\Inetpub\loans\Admin\Issue.aspx.vb    Line: 109 Stack Trace: [SqlException (0x80131904): Error converting data type varchar to numeric.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendT...

Error converting data type varchar to numeric
hi All, This case statement is giving me the error "Error converting data type varchar to numeric". the column dbo.tblKMTermloanFees.base_min datatype is varchar.  And please see below for sample data in the column. i have to convert this into number. Any suggestions guys how to do this, Thanks in advance. Shilpa.  CASE  WHEN dbo.tblKMTermloanFees.base_min LIKE '%/6M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 6 WHEN dbo.tblKMTermloanFees.base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Mi...

Error when Convert or Cast functions from Varchar to XML datatype
Hi I have a varchar(8000) and currently XML files are stored in varchar(8000).Some times when i am doing manuplactions in my varchar column i am getting with special characters error. so now i want to keep my column varchar(MAX) and when i am doing calculations i will convert my varchar datatype to xml datatype. By doing this i hope there wont be any special character problems. When i am doing calculations with the wellformed xml i am getting error for both convert and cast methods as below  I am trying to do convert(xml,MyVarcharColumn) Implicit conversion from data ...

Problem with function (Converting data type varchar to bigint)
I get the following warning in visual studio 2005 for this function(bottom of page)."Warning    1    Function 'check_availability' doesn't return a value on all code paths. A null reference exception could occur at run time when the result is used.    C:\Documents and Settings\XXX\My Documents\Visual Studio 2005\WebSites\Loans\Admin\Issue.aspx.vb    492    5    C:\...\Loans\" When I run the site in debug mode I get this...Error converting data type varchar to bigint. Description: ...

Problem using ODBC: Numeric fields converted to string.
When linking a table in Access 97 or opening the table in Visual Basic 5 using DAO, fields of type numeric, money etc. are converted to strings. This makes it difficult to format the fields correctly. This (could) be posible to accept, but the decimal is also converted to . (dot). I am using , (comma) in the regional settings. Is this a known bug? Any workarounds? Thanks, SH (Please remove NoSpam. from my mail-adress) ...

Using of functionality "Convert to web application" programmatically
We wrote several months ago the “application” which is able to generate the ASP.NET code from some proprietary metadata. Now, we use the new Web Application project which has the new *designer.cs file.SO, we are planing to extend our code to generate this file too. However, the fact is that this file is directly generated from the ASPX file. Before we start to do it, it would be useful to use out the same functionality you use.Is there any public functionality in the WebApp project type, which we can utilize for this purpose?ThanksDamir...

Error editing data records in gridview when using convert function.
Hello, i currently have a gridview that is populated with data from a SQLServer datasource. I have put an output mask in the select statement, so the date and time attributes are displayed in the format i prefer them to be in. SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment];  However when i click the 'edit' link for a record in the gridview, i am unable to edit the date/time attributes and when i click update to confirm any changes to the other attributes, the values in the date/time attributes are...

Convert() function
Hi, I have found some ways on using Convert() function for SQL server. But i do not understand some of them. Example: CONVERT(VARCHAR(10),column name,108) What is the 108 for? I see some with 120 and 101. I tried all but only 108 is working for me the way I want..but i do not understand what is that for actually. Can anyone explain it to me? Thank you. Hi, You can get the time part of a date column value as varchar by using the convert function with style 108 Say, select top 1 ApplicationDate from Applications returns "2003-04-11 15:20:00" Then SELECT top 1 CONVERT(VARCHAR(10), A...

CONVERT function
I am still using ASA 7, I had the following records, they are varchar(20) datatype 07.09.2007 11:12:00 ---> wants to be 2007-09-07 11:12:00 08.09.2007 11:13:00 ---> wants to be 2007-09-08 11:13:00 09.09.2007 11:14:00 ---> wants to be 2007-09-09 11:14:00 10.09.2007 11:15:00 ---> wants to be 2007-09-10 11:15:00 and I want to convert it into datetime type, I did as follow but does not work: SELECT CONVERT(VARCHAR(20), order_date, 121) FROM sales what did I miss? Regards Winanjaya Winanjaya, I did not try this, but how I understand the documentation, conv...

Web resources about - Using CONVERT function to convert NUMERIC to VARCHAR - sybase.ase.general

Talk:Trigonometric functions - Wikipedia, the free encyclopedia
This article is within the scope of WikiProject Mathematics , a collaborative effort to improve the coverage of Mathematics on Wikipedia. If ...

Eating chocolate 'improves brain function' - study
... news for those with a sweet tooth - a recent study has found that regular chocolate consumption is associated with better cognitive function. ...

Using Functions to Make Better Abstractions
... (code that is tightly coupled to its own implementation details). I was wrong. I was wrong because I had an incomplete understanding of functions. ...

Cisco launches new network functions virtualization infrastructure solution to help service providers ...
Al-Bawaba Cisco launches new network functions virtualization infrastructure solution to help service providers run network ... Al-Bawaba ...

Google Maps for iOS adds detour function, 3D touch support
Google has extended its pit-stop feature the ability to find places to stop off on journeys to iPhone and iPad users with the release of Google ...

LVMH's Hublot May Be Next Brand for Smart Functions, Biver Says
Hublot will probably be the next LVMH watch brand to add smart functions, following TAG Heuer’s footsteps in combining Swiss craftsmanship and ...

Yahoo Begins Grim Job of Closing Functions, Laying Off Staff
Yahoo Food is closing, and other digital magazines on tech, travel and beauty are also scheduled for closing or major cutbacks.

Compliance Risk Study Shows Compliance Function Needs New Initiatives to Protect Its Hard-Won Gains
Financial institutions’ compliance functions have come a long way, but they still have some distance to travel before they reach the right strategic ...

A 3D Printed Jet Engine Appears to Function
... jet engine . You may remember seeing a 3D printed jet engine grace our front page back in October. That one was beautiful didn’t function . ...

‘World Of Warcraft’ Alpha Removes Gold From Garrison Missions, Water Striders No Longer Function Everywhere ...
... build deploying soon to the alpha server, garrison missions no longer reward gold and the water-walking Water Strider mounts no longer function ...

Resources last updated: 3/24/2016 1:22:05 AM