Few of the questions I receive very frequently. I have collect them in spreadsheet and try to answer them frequently.
How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.
How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table
Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.
Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.
How to parse/retrieve only numbers from column values contains alpha-numeric characters?
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
What are the online references for CAST and CONVERT?
CAST and CONVERT
Reference : Pinal Dave (https://blog.sqlauthority.com)
439 Comments. Leave new
Hi..
I want to convert ‘1,2,3,4,5,6,7,8,9,10,11,12’ this string to numeric
when i convert it show ‘Error converting data type varchar to numeric’.
plz help
SELECT CONVERT(INT, CONVERT(CHAR(10), GETDATE(),112 ))
SELECT CAST(CONVERT(CHAR(10), GETDATE(), 112) AS INT)
which is better in this
–only values composed of digit
SELECT column1 FROM table WHERE column1 not like ‘%[^0-9]%’
Hi….
i executed below commands successfully….
create table DMART (ID int identity(1,1), PARTICULARS VARCHAR(20), QUANTITY SQL_VARIANT, RATE DECIMAL(20,1), VALUE DECIMAL (20,2))
SELECT * FROM DMART
INSERT INTO DMART (PARTICULARS, QUANTITY, RATE) VALUES(‘REDGRAM’,2.008, 70.50), (‘SUGAR’, 1.002, 35.50)
INSERT INTO DMART (PARTICULARS, QUANTITY, RATE) VALUES(‘RAVA’,1.048, 30.50), (‘CHOPPING’, 1, 139.00),
(‘BATH MESH’, 1, 21.00), (‘BELT LADIES’, 1, 99.00), (‘BRITANNIA G’, 2, 26.00), (‘BROOKE BOND’, 1, 107.00),
(‘CHICKEN MASALA’, 1, 18.00), (‘MAAZA’, 1, 28.00)
My doubt is…
unable to perform below task….
update DMART set VALUE=QUANTITY*RATE
The error is:
Msg 260, Level 16, State 3, Line 1
Disallowed implicit conversion from data type sql_variant to data type decimal, table ‘DMART’, column ‘QUANTITY’. Use the CONVERT function to run this query.
plz help me…
Hi Kranti,
you need to use convert function for updating the query.
use below query it will help you …
update DMART SET VALUE=CONVERT(DECIMAL(20,3),QUANTITY)*RATE
Hi , I want convert alphanumeric to numeric. the conversion should be like this , example input is abt538z , the output should be . 122053826(a =1 ,b=2,c=3……z=26)……….. any body tell me the code………..
Hi, Say I have one EMPLOYEE table which has one column “Date” (frmt YYYYMMDD) which is Number and another string column “REQ_DATA”. Now this “REQ_DATA” contains value which has many character including date in following frmt DD/MM/YYYY e.g. ABCDEFGDD/MM/YYYY
Is it possible any how to find out only those records from EMPLOYEE table where these two dates are equal?
Hello, I am trying to convert text into numbers. The variable “`DatabasNyaKunder$`.`Antal lgh`”. I use an excel file as data base.
The sql-query looks like below.
SELECT `DatabasNyaKunder$`.`Ordernr / offertnr`, `DatabasNyaKunder$`.Kundnr, `DatabasNyaKunder$`.Namn1, `DatabasNyaKunder$`.`Antal lgh`
Thank you, best regards Gustav
i have 3 seperate text columns containing day,month and year, how do i combine them in date format
regards
kpb
I want to get only integral part in string
for Example string contain ab123ce234fe means i want only interger part like 123234
how can i get
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @str VARCHAR(8000)
SELECT @str = ‘ab123ce234fe’
SET @loop = 0
WHILE @loop < 26
BEGIN
SET @str = REPLACE(@str, CHAR(65 + @loop), '')
SET @loop = @loop + 1
END
SELECT @str
I am trying to create a URL from SQL (select). I have written the first part as text and the final part of the URL is taken from a column within the table. As the text is varchar. I need to convert this to ‘int’. Not sure if this is possible!
select CAST(‘<a href="http://' + per.DepartmentID + '’ as int),
hi,
How to convert (1500.000) + (1000.00) + 1000.000 (string value) to 2500.000 (numeric) .
please advise.
Thanks in Advance.
I have a varchar(50) value ‘903403+ ‘
How do I cast it in Integer? I want a SQL query which will make this value Integer without + sign.
will you always have + symbol at the end? You can replace that with blank and then convert/cast?
plz give the meaning for this
‘DATEADD(MINUTE,CAST(TIMEZONE AS INT),GETUTCDATE())’
I get Conversion failed when converting the varchar value ‘436603J’ to datatype int. LearnerID is of varchar(12). I tried CONVERT(int, LearnerID) but doesn’t work…Please help!
Hi sir,
Awaiting for your response.
Thanks,
I want to convert 1250 to 12.50 , 5 to 0.5. how to convert
Saludos desde España y gracias de antemano.
dado el resultado de un cuadro de lista:
list1=”125455,12″
como puedo pasar el valor de list1 (carácter) a cuadro de texto (text1 en formato numérico)
esto es:
text1(numérico)=list1(carácter)
resultado:
Text1=125455,12
list1=”125455,12″