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 am new with this , i need help plz
i am trying to calculate max number and add 1 on it
the field called numb , and the max number on it is 00047 but its character( not numeric ) i want the result to be 00048 and replace it
select 1
CALCULATE MAX(NUMB) TO MYNUMB
SELECT 3
REPLACE NUMB WITH ‘MYNUMB+1’
any help plz
select MAX(NUMB*1) +1 from table
Hi , Its Gud
I am using Visual Studio 2008 to develop an ASP.NET VB application for a client. The database is MS ACCESS, which they provided. I created an AccessDataSource to make the connection to the DB and am using a FormView control on the page to display, edit, delete and insert records.
The FormView control provides various methods for displaying the data, most of which are of the TextBox variety, which is derived from ‘Templates’.
Viewing works fine, as does inserting new records. The problem I’m having is in Update and Delete. The reason for that, I am sure, is that the index (primary key) into the Access database table is an ‘Auto Number’ (Long Integer), which is displayed in the template inside a TextBox, which means it’s now a string and has to be converted (CAST) back to an Integer. This applies to both the UPDATE and DELETE strings.
Here is the DELETE string, as coded in the AccessDataSource template:
Result = the table
ID = the index into the table (primary key)
@IDLabel1 = the TextBox where the index (ID) is stored/displayed
DELETE FROM Results WHERE ID=CAST(@IDLabel1 AS INT)
The error returned in MSIE is;
Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression ‘[ID]=CAST(@IDLabel1 AS INT)’.
I haven’t gotten around to modifying the UPDATE statement yet and am assuming that once the DELETE statement problem is fixed, I can apply the same solution to the UPDATE statement.
Again, the contents of the TextBox (@IDLabel1) is the index into the table that identifies the record to be either DELETED or UPDATED. Any help would be greatly appreciated.
Thanks.
OldFart
Never mind. I figured it out. The error was due to NOT having populating the ‘Keys/DataKeyNames’ array in the asp:FormView section of the code.
By default, it (apparently) assumes that ALL fields are required to Update and/or Delete a record, which obviously isn’t the case. I added the primary key (“ID”) to the variable (i.e. DataKeyNames=”ID”), and Christmas came a couple of days early this year.
The whole thing is controlled by the ‘Keys’ dictionary. To quote MicroSloth;
“The Keys dictionary contains the names and values of fields that uniquely identify the record to update or delete, and always contains the original values of the key fields. To specify which fields are placed in the Keys dictionary, set the DataKeyNames property to a comma-separated list of field names that represent the primary key of your data.”
All this (and MUCH more) can be found in the MSDN article entitled; “Modifying Data Using a FormView Web Server Control”
Although everything is working just swell now, I think MS goes out of their way to make things as convolulted as they possibly can. I guess job security is REAL important to their developers. Go figure.
OldFart
Almost forgot. I didn’t have to use CAST, CONVERT or CInt or anthing else to get the Update and Delete commands to execute properly. Here’s why;
Apparently, the orginal values and their data types are all stored in the “Values dictionary collection”, which basically means that when those values are referenced/used, they are automatically converted back to their original data types and are used accordingly. Pretty clever. There are actually THREE dictionaries; the Keys dictionary, the NewValues dictionary, and the OldValues dictionary (what happened to the ‘MiddleAgedValues’ dictionary?!).
I’ve been developing software since 1975 (hence the ‘OldFart’ handle). Some of this new stuff is completely foreign to me and takes a while to sink in. This one took me 2 days to figure out. I’m learning … all over again.
OldFart
select cast(‘john’ as int)
–gives me error
–Conversion failed when converting the varchar value ‘John’ –to data type int
It is very clear. How can you convert characters to int?
hello sir ,i’m karthik from india i have one quesition in sql server
one field have “dep1”
how to split character and numeric .
how numeric values only increment like “dep2”
please reply the answer
thank you,
karthik.
select col,’dep’+cast(max(substring(col,4,1))*1+1 as varchar(10)) from table
what is the difference between cast and convert function? explain with simple example?
This post exactly describes what you needed
Hi,
How to convert in sql,
example number
1) 43 to be 0043
2) 3 to be 0003
Any suggestion?need help..
Thanks
Better to do it in front en application. Otherwise
select right(‘0000’+cast(col as varchar(10)),4) from table
hi all,
i want to convert a numeric value into text. i’m using this code and this is successfully, but if i enter value such as 1234.56. the output will be One thousand two hundred thirty four.
the decimal value which is (.56) not appear. how i want to make the coding read the decimal value.
here is my coding
CREATE FUNCTION NumToWords
(@num numeric)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@Num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
SET @res = ”
DECLARE @tblNum TABLE
(Num int, NumStr varchar(20))
INSERT INTO @tblNum
SELECT 1, ‘ ONE’ UNION SELECT 2, ‘ TWO’ UNION
SELECT 3, ‘ THREE’ UNION SELECT 4, ‘ FOUR’ UNION
SELECT 5, ‘ FIVE’ UNION SELECT 6, ‘ SIX’ UNION
SELECT 7, ‘ SEVEN’ UNION SELECT 8, ‘ EIGHT’ UNION
SELECT 9, ‘ NINE’ UNION SELECT 10, ‘ TEN’ UNION
SELECT 11, ‘ ELEVEN’ UNION SELECT 12, ‘ TWELVE’ UNION
SELECT 13, ‘ THIRTEEN’ UNION SELECT 14, ‘ FOURTEEN’ UNION
SELECT 15, ‘ FIFTEEN’ UNION SELECT 16, ‘ SIXTEEN’ UNION
SELECT 17, ‘ SEVENTEEN’ UNION SELECT 18, ‘ EIGHTEEN’ UNION
SELECT 19, ‘ NINETEEN’ UNION
SELECT 20, ‘ TWENTY’ UNION SELECT 30, ‘ THIRTY’ UNION
SELECT 40, ‘ FOURTY’ UNION SELECT 50, ‘ FIFTY’ UNION
SELECT 60, ‘ SIXTY’ UNION SELECT 70, ‘ SEVENTY’ UNION
SELECT 80, ‘ EIGHTY’ UNION SELECT 90, ‘ NINETY’
DECLARE @hundred varchar(200)
SET @hundred = ”
DECLARE @separatorUnit varchar(20)
DECLARE @nStr varchar(20)
WHILE @place 0
SET @res = @hundred + @separatorUnit + @res
SET @hundred = ”
END
SET @place = @place + 1
END
IF @hundred ” SET @res = @hundred + @separatorUnit + @res
RETURN @res
END
sorry,this is the full coding.
CREATE FUNCTION NumToWords
(@num numeric)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@Num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
SET @res = ”
DECLARE @tblNum TABLE
(Num int, NumStr varchar(20))
INSERT INTO @tblNum
SELECT 1, ‘ ONE’ UNION SELECT 2, ‘ TWO’ UNION
SELECT 3, ‘ THREE’ UNION SELECT 4, ‘ FOUR’ UNION
SELECT 5, ‘ FIVE’ UNION SELECT 6, ‘ SIX’ UNION
SELECT 7, ‘ SEVEN’ UNION SELECT 8, ‘ EIGHT’ UNION
SELECT 9, ‘ NINE’ UNION SELECT 10, ‘ TEN’ UNION
SELECT 11, ‘ ELEVEN’ UNION SELECT 12, ‘ TWELVE’ UNION
SELECT 13, ‘ THIRTEEN’ UNION SELECT 14, ‘ FOURTEEN’ UNION
SELECT 15, ‘ FIFTEEN’ UNION SELECT 16, ‘ SIXTEEN’ UNION
SELECT 17, ‘ SEVENTEEN’ UNION SELECT 18, ‘ EIGHTEEN’ UNION
SELECT 19, ‘ NINETEEN’ UNION
SELECT 20, ‘ TWENTY’ UNION SELECT 30, ‘ THIRTY’ UNION
SELECT 40, ‘ FOURTY’ UNION SELECT 50, ‘ FIFTY’ UNION
SELECT 60, ‘ SIXTY’ UNION SELECT 70, ‘ SEVENTY’ UNION
SELECT 80, ‘ EIGHTY’ UNION SELECT 90, ‘ NINETY’
DECLARE @hundred varchar(200)
SET @hundred = ”
DECLARE @separatorUnit varchar(20)
DECLARE @nStr varchar(20)
WHILE @place 0
SET @res = @hundred + @separatorUnit + @res
SET @hundred = ”
END
SET @place = @place + 1
END
IF @hundred ” SET @res = @hundred + @separatorUnit + @res
RETURN @res
END
the following sql is not working.give a solution
Select isnull(max(convert(int,substring(BookCode,2,4))),’S’)
from BookMaster
Hi,
i have a field of varchar data type, i want to use the “in” clause in the query to get the data,how it is possible?
e.g the data in the field is like ‘50,60,70’ i want to get the data. the query is like
select * from tbl_test where cloumn_name in (’60’)
here tbl_test is the table name and column_name is the field name where ‘50,60,70’ exist.
plz help me i searched alot but not get any solution..
thanks in advance.
Hi Dear
I have problem.I have two fields in my table as ID and Sub_ID.
ID column is for my items(1,2,3,….) and SUB_ID is for save sub items for each item Like (1-1,1-2,1-3 ,…..)
this is a sample 10 row of my table :
item sub_item
==================
1 1-1
1 1-2
1 1-3
1 1-4
1 1-5
1 1-6
1 1-7
1 1-8
1 1-9
1 1-10
1 2
1 3-1
1 3-2
……..
When I use this :
SELECT * FROM MyTable
ORDER BY ID, Sub_ID ASC
items 1-10 comes before 1-1 on sort like
1 1-10
1 1-1
1 1-2
……
How can I solve this problem?
I used your comments in this page nd i wrote this:
select id , substring(Sub_ID,1,(charindex(‘-‘,Sub_ID)-1)) as new_sub_id
but it could’nt work.Can u help me Please?
Kind Regards
BAbak
HI,
Were you able to find a solution for the above problem?
Hi all,
i hope you can help me
in a table EventLog, i have a column nDaeTime(INT) in secondes( from 1970/01/01).
I want to convert and write all the value of nDateTime(INT) to a column in a separate table named LogRecord in the column DateTime(varchar(50) with the following format: DD/MM/YYYYHH:MM:SS.
i.e:
tbl_EventLog tbl_LogRecord
nDateTime DateTime
1271860947 ———-> DD/MM/YYYYHH:MM:SS
How can i do it ?
many thanks,
laurent
Tengo un problema, tengo la tabla empleado(codigo,nombre) el caso es que “codigo” tiene valores ‘XP0001′,’XP0002’,’XP0003’… lo que quiero es insertar nuevo empleado desde una pagina web pero como entrada solo tiene “nombre”; y “codigo” debe autogenerarse, osea al insertar nuevo NOMBRE de empleado, internamente el SQL debe insertar el nuevo CODIGO de empleado, como lo hago???
gracias.
how can i select number of thousands in a sum of amount
in sql 2000
ex: 56780 means i need the result as 56 thousands
I have the data like ‘200911’ ,’201124′,’AVV,CVR,BXY’
in which ‘200911’ is the start date( i.e 2009 is the year and 11 is the work week
and the second value is ‘201124’ is the end date (i.e 2011 is the year and 24 is the work week). and the next value is the first 3 letters of the record name… which is a multiple list of record names
I need to pull the records from a table called sn_master that consists of a ship date in it which is in standard date format…
I need to pull the records from the starting day of the given first parameter value of work week to the ending day of the second parameter value of the work week following the 3 characters of the record name in the where condition…
for this above conditions… I have written this query which is working fine in 4 seconds… but I’hv been told that to write directly with out using parameter @sqlquery… as user dont have exec permissions, so I was trying to run directly removing it but its taking long time of 4 to 5minutes, can anyone help me whts the problem was….
1.
SET @sqlQuery = ‘INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(”0” + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”’+ @startdate + ”’AND
shpm.shipdate =”+ @startdate + ”AND
shpm.shipdate <='' + @enddate + '' AND
snm.sn like ''+ @sn + '%' order by shpm.shipdate desc
Kindly help me out where its getting wrong…..?
and here is the query without @sqlquery parameter
INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(‘0’ + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”+ @startdate + ”AND
shpm.shipdate <='' + @enddate + '' AND
snm.sn like ''+ @sn + '%' order by shpm.shipdate desc
Kindly help me out where its getting wrong…..?
How do you maintain the value of a number that is being received as an int? For example, I have the value ‘0123’ defined as a varchar(4) in one table and value ‘123’ being received in another table. I need that leading zero. How do I convert the data type, but not lose the leading zero?
If the datatype is integer it is not possible