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
Hello
Can somebody kindly help me with this?
I have a number stored in a table as below:
100258
When i am using the function substr(100258, 2,) and export to a CSV file, i get the output as 2 but i desire to get the output as 002.
@Raman
It isn’t so clear what you want. Try this:
SELECT ’00’ + SUBSTRING(CAST(100258 AS VARCHAR), 4, 1)
I am getting following error
Conversion failed when converting the varchar value ‘Null’ to data type int
when I try to run following query
SELECT DISTINCT
CommTypeEID=CONVERT(VARCHAR(50),
dbo.SalesChannels.UserLevel)+’;’+U1.EID+’;’+CASE WHEN U1.EID IN (‘E023456′,’E04342′,’E07778′,’E045454′,’E068660′,’E110218′,’E074466′,’E079211′,’E014307′,’E045044′,’E081162′) THEN ’17’ ELSE CONVERT(VARCHAR(50), dbo.CommissionPlan.CommissionPlanID) END,
U1.LastName + ‘, ‘ + U1.FirstName + ‘ (‘+ U1.EID + ‘;’ + dbo.CommissionPlan.Description+’)’ AS [AgentName],
U1.lastname, U1.firstname
FROM dbo.mydatabase
Please help me
Im going nuts with this, and I know I more info may need to be provided to answer this correctly, so bear with me. Whoever made the db I’m working with, has money being stored into the db as char(8). For example, the amount $1.39, looks like 0000139 in the table. How can I put the decimal back into the right spot?
I’ve tried many variations of convert/cast and can;t seem to get it right. I need to build a script that does this, please help?
I also would need to drop any leading zero’s if applicable.
For example, an amount of $1,000.00, is showing up in the db as 0100000. Juts for more info, the name of the column is xx_amount, and the data type was made as char(8). Thanks so much.
I had some spare time so I wrote a little function. Try this and see if it works for you.
CREATE FUNCTION StringToMoney(@str VARCHAR(MAX), @DecSep CHAR(1), @ThousandSep CHAR(1))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @ret VARCHAR(MAX)
— Take out leading zeroes
DECLARE @value VARCHAR(MAX)
SET @value = SUBSTRING(@str, PATINDEX(‘%[^0]%’, @str), LEN(@str))
— Take decimals
DECLARE @decimals VARCHAR(2)
SET @decimals = SUBSTRING(@value, LEN(@value) – 1, LEN(@value))
SET @decimals = @decimals + REPLICATE(‘0’, 2 – LEN(@decimals))
— Take characters before decimal (what are those called anyway?)
SET @value = SUBSTRING(@value, 0, LEN(@value) – 1)
IF LEN(@value) = 0 SET @value = ‘0’
— Insert thousand separators (this would be eeeeaaasy with regexps)
DECLARE @value2 VARCHAR(MAX)
SET @value2 = ”
WHILE LEN(@value) > 3
BEGIN
SET @value2 = CASE WHEN LEN(@ThousandSep) = 0 THEN ” ELSE @ThousandSep END + SUBSTRING(@value, LEN(@value) – 2, LEN(@value)) + @value2
SET @value = SUBSTRING(@value, 0, LEN(@value) – 2)
END
SET @value2 = @value + @value2
SET @ret = @value2 + @DecSep + @decimals
RETURN @ret
END
Is there system Tables/views/sps/fucntions to list the all date time format styles which is an input for convert function?
Read about Convert function in SQL Server help file
It lists out all formats
Thanks for ur immediate reply.
i want to know the style at rum time.if i give the format ‘yyyy-mm-dd’ at run time,i should get the style.is it possbile?(i dont want to hardcode the style)
Why do you want to do this?
If you use front end application, use the format function there
Sir,
Sql parameter p;
Sql connection cn=new sqlconnection(” “);
sql command cmd= new sqlcommand(…);
cmd.CommandType=CommandType.Storedprocedure;
p = new SqlParameter(“@Firstname”, SqlDbType.VarChar);
p.Value = Convert.ToString(txtFName.Text);
Can i pass this p value to Int EmpId?
How can i pass it?
Why dont you try and see if it works?
Hi,
I have a column of type decimal.I want to convert it into foramt HHMMSS,
If i use Convert(datetime,field,8) ,and the field contains 42 then the o/p is 1900-01-01 00:00:00,but i want it as 00:00:42.
Could anyone please tell me what i am missing here?Is it possible to do this conversion without using DATEADD?
select convert(varchar(8),dateadd(second,42,0),108)
Hi,
I have a column of type decimal.I want to convert it into foramt HHMMSS,
If i use Convert(datetime,field,8) ,and the field contains 42 then the o/p is 1900-02-12 00:00:00,but i want it as 00:00:42.
Could anyone please tell me what i am missing here?Is it possible to do this conversion without using DATEADD?
select convert(varchar(10),dateadd(second,42,0),108)
Thanks,But is it possible without using DATEADD?
@chaitra
Perhaps something like this will work:
SELECT CONVERT(DATETIME, ’12:00:’ + ’42’, 14)
Why?
select convert(varchar(10),cast(’00:00:’+’42’ as datetime),108)
hi.
i have data(double) from table then i want convert into format ###,###,###,###.##
my data :1234567890.25
how convert into : 1,234,567,890.25
tq
This is the formation issue you should do this in your front end application
Hi All,
I need help in SQL server 2008 query.
Table structure is
ID Site Linked ID
1 100 2,3,4
2 200 4,5,6
5 100
On a search form, three fields are available. Those are id, Linked_Id, site.
1) On search form , if I select linked_id option and site = 100 then returned records should contain all records which belong to site ‘100’ and records which are linked to those ids. In this case, return records should be having ids 1,2,3,4,5.
2) If I give value as ‘1’ in id field on search form and select linked_id option and site = 100 then in this case it should return records having ids 1,2,3,4.
Please help me in forming this query.
I couldnt find the current discussion regarding such query. So, giving my query in this thread. If possible, Please redirect it to correct thread. Thanks !!!
Thanks in advance.
Read about Commom Tabe Expression
You will get some idea on how to implement this
hi sir,
I learned lot about sql queries from your website. it’s simple and useful.
with regards,
A.Moorthy
Hi i would like to know the datatype of int
we have the option of number(4,2) in oracle
like this way we have anyother datatype is available in server
You can’t specify length for int datatype
You can however specify the same for decimal datatype
Hi all,
How can I convert a money value like 539393.00 (in milliseconds) to
time 00:08:59:393 (hh:mm:ss:ms)
in MS SQL
Kind Regards,
Luc
select dateadd(millisecond,539393,0)
thanks, it works fine.
but I only need the time, how can I remove the date?
I’m really greatfull and appreciate your help.
Where do you want to show data?
If you use front end application, do formation there
Otherwise
select left(right(convert(varchar(30),dateadd(millisecond,539393,0),109),11),9)
Hi all, my last problem
The next stored procedure works fine
use ExamenLoopClub
go
if exists
(select name from sysobjects
where name = ‘sp_WedstrijdAll’ and xtype = ‘p’)
drop procedure sp_WedstrijdAll
go
create procedure sp_WedstrijdAll
as
SELECT (w.WedstrijdID) as ID,
left ((convert(varchar(20),(w.Startijd), 113)),17) as Starttijd,
(w.Wedstrijdnaam)as Naam, ( i.LoperID)AS [#Deelnemers]
FROM tblWedstrijd w left join tblInschrijving i
ON w.WedstrijdID = i.WedstrijdID
The table tblWedstrijd contains more different WedstrijdID than tblInschrijving , so in that case the result is null.
Now I must count all the LoperID and show the result near Wedstrijdnaam. I change the SP to
use ExamenLoopClub
go
if exists
(select name from sysobjects
where name = ‘sp_WedstrijdAll’ and xtype = ‘p’)
drop procedure sp_WedstrijdAll
go
create procedure sp_WedstrijdAll
as
SELECT (w.WedstrijdID) as ID,
left ((convert(varchar(20),(w.Startijd), 113)),17) as Starttijd,
(w.Wedstrijdnaam)as Naam, count(distinct i.LoperID)AS [#Deelnemers]
FROM tblWedstrijd w left join tblInschrijving i
ON w.WedstrijdID = i.WedstrijdID
group by (w.Wedstrijdnaam)
I get : Column ‘tblWedstrijd.WedstrijdID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
What’s the sollution for this?
This is my last SP, the the rest is CSharp (datagridview)
Thanks a lot for the help
I found it, Great site with study info, thanks a lot.
use ExamenLoopClub
go
if exists
(select name from sysobjects
where name = ‘sp_WedstrijdAll’ and xtype = ‘p’)
drop procedure sp_WedstrijdAll
go
create procedure sp_WedstrijdAll
as
select (w.WedstrijdID) as ID,
left ((convert(varchar(20),(w.Startijd), 113)),17) as Starttijd,
(w.Wedstrijdnaam)as Naam, y.[#Deelnemers]
from tblWedstrijd w cross join (
select distinct w.WedstrijdID , count(distinct loperid) as [#Deelnemers]
from tblInschrijving i right join tblWedstrijd w
on w.WedstrijdID = i.WedstrijdID
group by w.WedstrijdID
) as y
where w.WedstrijdID = y.WedstrijdID
order by w.Startijd desc
Hi!! This is a really nice blog. I hope you will be able to help me solve my problem.
I am creating a report using sql server 2005 and I need to convert numbers to characters like 345000 top three hundred and forty five thousand. Is this possible? If it is, please tell me how to go about it. Thank you
Crystal Reports has a function called toWords that can do this job. See if you are able to find a similar function in SSRS
I personally use CONVERT. One thing I like about your posts are that, you always compare similar methods of doing the same thing. This is very important to actually know the inner workings of those methods.
And the UDF to extract numeric from alpa-numeric is excellent !!!
Note that CAST is ANSI standard and CONVERT is not
You can use CONVERT for formatting the dates and money values
Hi
I have a table which is char datatype with data’s as 45689MB.63, 89586.21MB, 365MB like wise… I need to convert into just numericals without MB like 45689.63, 89586.21… i tried
select cast (column_name, float) from table_name
select cast (column_name, float) from table_name
but getting error as:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
please help, i need to find out DB sizes of around 75 server once in a week, now i’m doing manually. If i convert char to float it will reduce my 80% of the work.
Thanks in advance
Chandru. . .
Try this
select cast (left(column_name,patindex(‘%[a-zA-Z]%’,column_name)-1) as float) from table_name
I want to add Like operator in the below query ,
Select Username,Password from For_login where
Convert(varbinary,Password )=convert(varbinary, ‘t’)
so that ,i changed my query like this
Select Username,Password from For_login where
Convert(varbinary,Password )=convert(varbinary, ‘t’)like ‘t%’
it gives error
Please correct my query
Thanks
What happens when you try this?
Select Username,Password from For_login where
Password ) like ‘t%’
Hello,
I have a column with a data type as Money having values :
209.90
8.30
29.20
Now I want these values to be converted to Hours
For example
If you see 209.90 consider figure before decimal that is 209 and after decimal its 90
Now 90 means 1hr 30 Min.
So output result of 209.90 should be 210.30
How to achieve the below Result?
210.30
8.30
29.20
Apply this logic
select times,parsename(times,2)+parsename(times,1)/60 as hours,parsename(times,1)%60 minutes from
(
select 209.90 as times union all
select 8.30 union all
select 29.20
) as t
Hello,
I want to run a query to multiply two columns in SQL SERVER 2008. Two columns looks like as follows,
Column1 Column2
45652 1year
54865 12 Months
The issue is, Result shall be calculated month wise. i.e. 54865 * 12 = XYZ. Need to convert year value in months as well to get the desired results.
Tried using CAST but its not working. It gives following error
‘Error converting data type varchar to numeric’
I’ll be grateful if someone can help !
Thanks.
You need to normlaise the table.
select column1*case column2 like ‘%year%’ then left(colum2,charindex(‘[a-z]’,column2)-1) else left(colum2,charindex(‘[ ]’,column2)-1) end from your_table