397

I am using Sybase and I am doing a select which returns me a column called "iftype", but its type is int and I need to convert into varchar. When I try to do the select without the convert function I get this error:

Error code 257, SQL state 37000: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.

I dont know how to implement the function CONVERT. Can anyone help me, please ?

potashin
  • 44,205
  • 11
  • 83
  • 107
Murilo
  • 4,453
  • 5
  • 19
  • 28

6 Answers6

666

Use the convert function.

SELECT CONVERT(varchar(10), field_name) FROM table_name
Taylor Brown
  • 1,689
  • 2
  • 17
  • 33
Tobberoth
  • 9,327
  • 2
  • 19
  • 17
  • 9
    According to the error, it's `VARCHAR` to `INT` but I'm answering his question, not the error message. – Tobberoth Nov 14 '13 at 14:07
  • 2
    Thanks. But now I got another error. When I try to do this kind of Select : SELECT CONVERT(varchar(10), field_name) FROM table_name. Or even the normal one like: SELECT field_name FROM table_name. Both are correct. I dont know why. But when I try to but a "where" at the end of the select, using the convert or not, I get the same error: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query – Murilo Nov 14 '13 at 14:09
  • 3
    @Murilo That's because the error is not from what you think. The error tells you that your code is trying to use a `varchar` where an `int` is needed. You need write your actual SQL statement for us to help you. – Tobberoth Nov 14 '13 at 14:10
  • Sorry guys. I found the error. In a part of the "where" I was trying to do id = '4' and the id`s type is int and not varchar. Sorry. And thanks for helping me ! – Murilo Nov 14 '13 at 14:13
  • 15
    @Tobberoth, for what it's worth, I just landed here by googling. Part of SO's value is getting answers to almost any question, even basic ones. – KyleMit Apr 08 '14 at 19:58
  • 7
    I think it should be `varchar(11)` in case the number is a large negative number. – Trisped Aug 25 '14 at 22:36
  • this just solved a huge bug on my code, the 200 users using it thank you. – dizad87 Mar 29 '18 at 02:03
129

Use the STR function:

SELECT STR(field_name) FROM table_name

Arguments

float_expression

Is an expression of approximate numeric (float) data type with a decimal point.

length

Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.

decimal

Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.

source: https://msdn.microsoft.com/en-us/library/ms189527.aspx

Mahdi
  • 1,778
  • 1
  • 21
  • 35
Rei Salazar
  • 101
  • 1
  • 3
  • 3
  • 5
    `STR()` should be the correct answer, it's simpler and less error-prone than the alternatives. – HerrimanCoder Nov 04 '17 at 23:01
  • 10
    For some strange reason, this didn't work for me as it was adding a tab in front of the text, no idea why. Selected solution did work though. – MaQy Nov 23 '17 at 19:27
  • 5
    str pads spaces on the front of the converted number making it useless – m12lrpv Apr 18 '18 at 01:20
  • 18
    Use LTRIM(STR()) – ashilon Nov 28 '18 at 13:08
  • 5
    STR() gives whitespaces on the front of the variable you convert to string. It can be a good choise to convert decimal variables but not for Int. – Ebleme Feb 27 '19 at 10:38
  • LTRIM(STR()) works perfectly with T-SQL. CONVERT(varchar(#), variable) can give unexpected results in T-SQL. – jdavid05 Apr 13 '19 at 16:56
  • 1
    STR will restrict length to 10 by default. SELECT str(1234567890.123) gives 1234567890 Where as ltrim gives much better result SELECT ltrim(1234567890.123) gives 1234567890.123 If you agree please upvote LTRIM – PAS Aug 24 '21 at 13:26
34

You can use CAST function:

SELECT CAST(your_column_name AS varchar) FROM your_table_name
Hamid Heydarian
  • 802
  • 9
  • 16
  • 3
    One can also try the same query "without specifying the length of the characters in varchar". >> *SELECT CAST(your_column_name AS varchar) FROM your_table_name * – Bandham Manikanta Nov 06 '21 at 07:17
25

Actually you don't need to use STR Or Convert. Just select 'xxx'+LTRIM(ColumnName) does the job. Possibly, LTRIM uses Convert or STR under the hood.

LTRIM also removes need for providing length. It seems to be working for integer or float without worry of truncation.

SELECT LTRIM(ColumnName) FROM TableName

also, LTRIM is better than STR as

SELECT STR(1234567890.123)

gives 1234567890 whereas

SELECT LTRIM(1234567890.123)

gives 1234567890.123

PAS
  • 1,791
  • 16
  • 20
  • This is the only answer that worked for me. Others are giving me "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax" – swifthing Mar 24 '23 at 18:40
  • @swifthing, glad it worked. Also, it's the best answer if you want to avoid truncation of decimals. – PAS Mar 24 '23 at 21:34
1
SELECT Cast(Cast([field_name] AS BIGINT) AS NVARCHAR(255))
FROM   table_name
RF1991
  • 2,037
  • 4
  • 8
  • 17
Zahid Hasan
  • 365
  • 3
  • 5
  • Please see [How to answer](https://stackoverflow.com/help/how-to-answer) for details on how to provide quality answers. It's useful to provide context and details on why this might be an ideal solution. – arkon Mar 19 '22 at 15:18
  • 1
    See "[Explaining entirely code-based answers](https://meta.stackoverflow.com/q/392712/128421)". While this might be technically correct, it doesn't explain why it solves the problem or should be the selected answer. We should educate along with helping solve the problem. – the Tin Man Mar 22 '22 at 04:49
0

CONVERT(DATA_TYPE , Your_Column) is the syntax for CONVERT method in SQL. From this convert function we can convert the data of the Column which is on the right side of the comma (,) to the data type in the left side of the comma (,) Please see below example. 

SELECT CONVERT (VARCHAR(10), ColumnName) FROM TableName
Tharuka Madumal
  • 201
  • 2
  • 5