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
You cannot convert character to Integer. Not using CAST and Convert function.
Thanks,
Imran.
gram1138 and Anand,
Thank you very much for the code! I had an idea on how to convert my string of numbers with varying lengths, but I didn’t quite remember all the string manipulation functions and I didn’t have enough time to research it again. But with your examples, I got it working in five minutes. Thanks!
Hello Sir,
The information there on this blog is very useful & helpful thanks for providing such informative infromation.
Sir i have a query in ms-sql server2000
can we convert numeric(18,0) to identity(1,1) which will be primary key ,
since this ID numeric(18,0) is a foreign key in another table so i wanted that this key should be as primary auto incrementing key
whereas i am also unable to do insert into statement for the same
error ocuuring is as : Cannot insert explicit value for identity column in table
Can you please help me out.
As error says, you need to omit identity column when inserting data to the table
Hi,
I want to convert output values for a status cloumn which are in char and I want to be displayed as a number just like below
ex:
status: status
green 1
yellow 2
red 3
Thankx
@Jothi,
you can use CASE function, I wrote a small example…
CREATE TABLE EXAMPLE1 ( STATUS VARCHAR(50))
GO
INSERT INTO EXAMPLE1 (STATUS) SELECT ‘GREEN’ UNION ALL
SELECT ‘YELLOW’ UNION ALL
SELECT ‘RED’
GO
SELECT * FROM EXAMPLE1
GO
SELECT CASE WHEN STATUS =’GREEN’ THEN 1
WHEN STATUS =’YELLOW’ THEN 2
WHEN STATUS = ‘RED’ THEN 3 END STATUS
FROM EXAMPLE1
GO
DROP TABLE EXAMPLE1
GO
Regards,
IM.
Please Tell me from wherei can download sql sever 2005
@Swathi.
You can download SQL Server Express Edition for free from this link.
http://www.microsoft.com/en-us/download/details.aspx?id=21844
You can also try for evaluation copy which is valid for 180 days ( 6 months ).
check this link for more details
https://www.microsoft.com/en-us/sql-server/sql-server-2016
Regards
IM
HI,
I am the beginner user of the SQL 2005.
May i know how to write a stored proc.
to convert integer into string without using CAST and CONVERT.
Any advise ?
thanks
Why do you want to do this?
Hi Dave,
i m Asma Qureshi here.
when ever i search any of my query in sql server then i found the solution from your blog
yes its very nice.
Thanks
Regards:
Asma Qureshi
Software Engineer
(ASP.Net2.0/3.0 , SQL server 2005/2008, Ajax)
*************************************
United Arab Emirates
(Dubai)
How can convert integer to time(hh:mm:ss) in sql server 2005. I am getting time as in seconds.
Please help me.
select dateadd(second,12345,0)
I got solution
SELECT CONVERT(varchar, DATEADD(second, 60, 0), 108)
Hi,
I had the following code, which worked perfectly with sql2000. @SerCD is a parameter taken in.
IF ISNULL(LTRIM(RTRIM(@SerCD)), ”) = ”
BEGIN
SELECT @SerCD = NULL
END
select @SerCD = case when @SerCD ‘ALL’ then
@SerCD
ELSE ‘%’
end
in the where clause , i had the following, to limit it be sercd
AND cdrFTS.SerCd LIKE convert(text,@SerCD)
However this does not work with sq2005, has something changed in relation to the convert in 2005?
thanks
mark
It should be
AND (@SerCD is null or cdrFTS.SerCd =@SerCD)
I want my application be localized. For this, I am using the regional settings to determine the appearance of the application. One part of it is the decimal delimiter. It can be either , or . (or other characters).
When I insert numbers into the DB, it is saved correctly (i.e. 4,5). However, when I try to convert it to a string (by using cast or convert) I am always getting the . decimal point (i.e. 4.5). I want to keep the number according to the regional settings, meaning that I am expecting the string to contain 4,5. How can I do it? What am I doing wrong?
Store it in varchar and dont convert it
But better approach is to use proper decimal datatype and do formation in your application
hi i have an issue
i’d like to convert to 3 significal number
ex
1.345 = 1.34
0.23 = .230
how is it possible?
thanks alot
husam
It is totally a formation issue. You should do this in your front end application
Also, what do you want to have for 87234.345?
@Husam
Below is a sample script to achieve your task. Remember one thing, when I created example1 table, datatype for the column is varchar, if in your case its not varchar then you have to convert it to varchar, you just have to use Varchar function, if you need help let us know.
Here goes your sample script.
— Create Example Table
CREATE TABLE EXAMPLE1 ( DECIMALVALUES VARCHAR(5))
— Create example data in our example Table, three different values that might exists,
INSERT INTO EXAMPLE1 VALUES ( ‘1.345’)
INSERT INTO EXAMPLE1 VALUES ( ‘0.23’)
INSERT INTO EXAMPLE1 VALUES ( ‘234’)
— Remember if you data is 4 characters length ex, 2345, you will see output as 234 only, so do a through testing before you use this script in your application.
— Check what you have inserted
SELECT * FROM EXAMPLE1
— ********Main Script**********
SELECT CASE WHEN CHARINDEX(‘.’, DECIMALNUMBERS) = 0
THEN SUBSTRING(DECIMALNUMBERS,1,3)
ELSE DECIMALNUMBERS
END DECIMALNUMBERS
FROM
(
SELECT CASE WHEN SUBSTRING(LEFT (DECIMALVALUES+ REPLICATE (‘0’,4) ,5),1,1) = 0
THEN SUBSTRING (LEFT (DECIMALVALUES+ REPLICATE (‘0’,4) ,5),2,4)
ELSE SUBSTRING (LEFT (DECIMALVALUES+ REPLICATE (‘0’,4) ,5),1,4)
END DECIMALNUMBERS
FROM EXAMPLE1
)X
Let us know if you need more help on this.
Regards,
IM.
Hello,
I need to convert 8 characters date ( ccyymmdd) into a 9 numeric (mmmddccyy)
example: 20090120 to be presented as 1202009
ccyymmdd mmmddccyy
Please help
Thank you
You should always use a proper DATETIME datatype to store date values
Try this
select replace(convert(varchar(10),cast(cast(20090120 as char(8)) as datetime),101),’/’,”)*1
@bghoh
select convert(varchar(2),datepart(month,convert (datetime, ‘2009.01.20’,102)))+ convert(varchar(2),datename(day,convert (datetime, ‘2009.01.20’,102)))+ convert(varchar(4),datename(year,convert (datetime, ‘2009.01.20’,102)))
If some one has a better way of doing this, please post it here.
Regards
IM.
I have posted the code
See my previous reply
I am posting here too
select replace(convert(varchar(10),cast(cast(20090120 as char(8)) as datetime),101),’/’,”)*1
Hi!
Can any one let me know what is the difference between cast and convert()?
Also between varchar and nvarchar
please also mail me on my email id
thanks n regards
These informations are availabe at SQL Server help file
I need help with converting a number into the string (e.g. 100 to be ‘hundred’). I am working with SQL Server 2005..
@Imran
Error in the SQL syntax for date conversion, not sure what is wrong. Please help . Thank you
insert into b0131 (ssn,lname,fname,relcode,sex,birthday,
othinscode,dateofdeath,marstatus,note,effdate, termdate)
(select m1.mem_altid,m1.mem_lname,m1.mem_fname,
(select case
when m1.mem_rel = ’01’ then ‘M’
when m1.mem_rel = ’02’ then
case
when m1.mem_sex = ‘M’ then ‘H’
when m1. mem_sex = ‘F’ then ‘W’
end
when m1.mem_rel = ’03’ then
case
when m1.mem_sex = ‘M’ then ‘S’
when m1. mem_sex = ‘F’ then ‘D’
end
when m1.mem_rel = ’04’ then
case
when m1.mem_sex = ‘M’ then ‘S’
when m1. mem_sex = ‘F’ then ‘D’
end
when m1.mem_rel = ’05’ then
case
when m1.mem_sex = ‘M’ then ‘X’
when m1. mem_sex = ‘F’ then ‘Y’
end
when m1.mem_rel = ’13’ then ‘G’
when m1.mem_rel = ’20’ then ‘P’
else ‘O’
end),
m1.mem_sex,
(select convert(varchar(2),datepart(month,convert
(datetime, m1.mem_dob.102)))+ convert(varchar(2),
datename(day,convert (datetime, m1.mem_dob,102)))+
convert(varchar(4),datename(year,convert (datetime,
m1.mem_dob,102)))),
m1.mem_cob, m1.mem_dod,m1.mem_mar,m1.mem_note1,m1.mem_eff,m1.mem_trm
from mem m1
where m1.mem_lev3 = ‘B0133’);