Use the convert function.
SELECT CONVERT(varchar(10), field_name) FROM table_name
Use the convert function.
SELECT CONVERT(varchar(10), field_name) FROM table_name
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
How do I convert these values (currently String) into INT (but with a catch)
Format Number as Text
Select Replace and convert from int to string
How to convert or cast int to string in SQL Server - Stack Overflow
Videos
Imagine I have this table list of values.
How do I convert all of these into INT?
And for those that have > in them, to automatically convert them into '600'.
So if it is >700, still '600'
(in my environment, everytime there is a >, it is 100% over 600, so I am okay setting it to '600'.
Thanks
You should convert. CONVERT(VARCHAR(4), your_col)
If you are looking for converting values in the column for your purpose to use in application, you can use this following-
SELECT CAST(your_column AS VARCHAR(100))
--VARCHAR length based on your data
But if you are looking for change data type of your database column directly, you can try this-
ALTER TABLE TableName
ALTER COLUMN your_column VARCHAR(200) NULL
--NULL or NOT NULL based on the data already stored in database
I have a case I need to show a string but the field I am looking at is int. Is there way to show the string Value when intfield > 0?
CASE WHEN [table].[intfield] > 0 THEN 'Value' ELSE [poop].[pee] END AS "Bathroom"
Consider an INT in SQL Server. It can be one of three values:
- NULL
- 0
- Not 0
So if you're casting/converting an empty string, which you are assuming is a number, then 0 is the most logical value. It allows for a distinction between NULL and 0.
SELECT CAST(NULL AS INT) -- NULL
SELECT CAST('' AS INT) -- 0
SELECT CAST('42' AS INT) -- 42
I'd say that's logical.
If you did:
SELECT CAST('abc' AS INT)
You'd get:
Conversion failed when converting the varchar value 'abc' to data type int.
If you do wish to handle empty strings as NULL use NULLIF as Bogdan suggests in his answer:
DECLARE @val VARCHAR(2) = ''
SELECT CAST(NULLIF(@val,'') AS INT) -- produces NULL
NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
Finally, if your columns are storing INT values, then consider changing its data type to INT if you can.
As you probably know NULL is a marker that indicates that a data value does not exist. And '' is a value, empty but value.
So MS SQL cast (or converts) empty value into 0 by default. To overcome this and show it as NULL you can use NULLIF
Simple example:
SELECT int_as_varchars as actual,
cast(NULLIF(int_as_varchars,'') as int) as with_nullif,
cast(int_as_varchars as int) as just_cast
FROM (VALUES
('1'),
(''),
(NULL),
('0')
) as t(int_as_varchars)
Output:
actual with_nullif just_cast
1 1 1
NULL 0
NULL NULL NULL
0 0 0
As you see NULLIF in that case will help you to get NULL instead of 0.