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 have a nvarchar value = ” Fee Component =Agency FeeÆFee=1600.00000000000000000ÆRemarks=Æ “, i want to convert the 1600.000000000 value to 1600.00 in this nvarchar value. So please suggest me the query for this.
Thanks,
Anki
Thank Bro.
You are a grate man. it helps me lot.
This is my select quary in sql 2005
SELECT EPFNo, yr, monthName, shftDate, shiftDesc, DateTim
FROM dbo.DayOffView
WHERE (DateTim >= CONVERT(DATETIME, ‘2012-03-15 00:00:00’, 102)) AND (DateTim <= CONVERT(DATETIME, '2012-4-17 00:00:00', 102)) AND
(EPFNo = '2374')
Use these approach for accuracy and error free
I have a column name WBS varchar(100) – values willl be like 27, 27.1,27.2 ,27.2.1 etc…
now i need to get max wbs from that table, but the problem is, it is giving properly upto 0-9 it crosess like if 27.9, 27.10 then it is giving only 27.9 as max WBS? but actually max wbs is 27.10
please give me some suggestions. its very important to me.
this is my Query:
(select max(wbs) from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)
i tried in following ways:
(select cast(isnull(max(WBS),0)as float) as wbs from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)
(select Convert(float,(isnull(max(WBS),0)) as wbs from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)
even itried for decimal also. didnt find any result.
First advise is to store these values in decimal datatype column. Try this
select max(WBS*1.0) as wbs from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396
Ya actually, intially our requirement was not like that thts y at tht time it is varchar recently it changed thts y, i tried to convert datatype to decimal bt its giving error tht error converting varchar to datatype decimal…
i tried the thing suggested by you,its not working..
canu please suggest another.
Thanks
What did you mean by “it is not working?” Did you get error?
I mean , same result is getting after trying urs also…
upto 0-9 only it taking 9 as max and giving result as 10…
You have multiple decimal points that can not be considered as numerals
SELECT TO_CHAR(TO_DATE(’10/10/10′,’YY/MM/DD’),’DD’)+MONTHS_BETWEEN(’10-OCT-10′,’10-AUG-10′)FROM DUAL;
What will happen when above statement is executed???
A.102 gets printed
B. Query fails as characters cannot be added to numbers
C. 12 gets printed
D. MONTHS_BETWEEN function fails
Plzzz explain
You have posted the code thats works in ORACLE. But this site focuses only on SQL Server
i came to know ans is 12 but how ???
select data.*
/*
,CASE WHEN data.counselor_name IS NULL
THEN c.combined_cem_quota
ELSE ”
END combined_cem_quota
*/
,CASE WHEN data.counselor_name IS NULL
THEN convert(DECIMAL(10,2),c.paf_quota)
ELSE ”
END ‘paf_quota’
,CASE WHEN data.counselor_name IS NULL
THEN convert(DECIMAL(10,2),c.heritage_quota) –c.heritage_quota
ELSE ”
END ‘heritage_quota’
FROM
(
select l.sup_emp_code,l.sup_name,raw.counselor_name,raw.sales_contract_nbr
,(SUM(raw.an_oc) ) l_an_oc
,(SUM(raw.heritage)) l_heritage
,(SUM(raw.merchendise)) l_merchendise
,(SUM(raw.paf)) l_paf
from
( select distinct sup_emp_code,sup_name from counselor_hierarchy where sup_emp_code 0 ) l
JOIN counselor_locations c on c.emp_no = l.sup_emp_code and c.override = 1
JOIN gor_data_raw raw on charindex(c.location_volume,raw.location_cd) > 0
GROUP BY l.sup_emp_code, l.sup_name,raw.counselor_name,raw.sales_contract_nbr with rollup
) data
JOIN counselor_locations c on c.emp_no = data.sup_emp_code
–WHERE data.sup_emp_code IS NOT NULL
Q. why is this program doing conversion?
I have put convert function but it still giving error?
define me convert function in sql
I need to convert a table of 1’s and 0’s to yes and no in SQL Server 2008.
I have no idea how to write the query. I am using a software that grabs table info from the server to update reports.
You can use case.
Case when fieldname = 1 then ‘yes’
when fieldname = 2 then ‘no’
else ‘-‘ end
Our company is converting over from an old HP3000 environment to an HP-UX environment. An ad-hoc reporting tool called DataExpress did calculations using actual characters such as “{” . We are attempting to convert those characters into SQL queries and of course we get an error: “Conversion failed when converting the nvarchar value ‘{‘ to data type int.” Has anyone ran into this before or perhaps can direct us where to look for a solution to this issue?
Hello sir,
We have a file in AS400 iseries that defined a numeric field as character filed,
when I view it in SQL, it showed up as 885Q, but it is actually 8858-, how do
I change this field to show up as “8858-” in SQL? Please help. Thanks
Fannie
Hi Dev how do we convert 10.2.123 to 10.2
How do I convert this
(select ‘Total Taxable:’+convert(varchar(12),sum (tax_val),)
from [pa-svr-man01].r_flosceola.dbo.vw_OpenAccts)
So that my answer will come back as 1,000,000,000
Thanks
I needed to be more specific my answer is coming back as 15098738203 and I want it to convert to 15,098,738,203
Thanks
Getting an error as “Error converting data type varchar to numeric.” for below statement. Can anyone suggest a way forward..?
select CONVERT(numeric,’24,00′)
I m looking for something that I can convert–
‘000-2.52’ should be -2.52
‘00002.52’ should be 2.52
I can do the convert(decimal(11,2), ‘00002.52’ ) but for negative I cannot do, please suggest, but the data that is coming in the file, so really can’t say when the data is postive or negative.
Please help.
I have put convert function its working Thx a lot
String Value Convert to INT, this query working thx a lot
Hello. I require help on a conversion from varchar to numeric.
I have nulls, 0.00, and numbers such as (134.75) to express negative values.
They are all in varchar data type. When trying various convert/cast functions, I am still not able to convert these figures into a numeric. I’ve tried removing the parentheses but to no avail.
I have a 14 digit varchar UPC code for example 00012580632101. I use either convert or case and it cuts off my leading zero digits. I need these to come out as they are relevant to the UPC number I need to use. How can I use cast or convert and keep my leading zeros?
Hi did you ever get a response or find a solution to this?
Hi everybody!
I’m pretty noobie in SQL Server, and I’m using the 2008 R2 version. I’m quite used to MSAccess and i have problems due the difference in functions names between the apps.
I have a column that holds time value, and it is a varchar. The data stored in it is like 10.58.03 and it is a time. I got how to gets only the minute [SUBSTRING(CONVERT(VARCHAR([TRN_TIME],108),4,2)] but it is a text, and I need to check if it is greater than 30, to group it.
But it is returned as String, and even addind CONVERT(INT,…) on it, it keeps returning string.
Can someone help me? Thanks in advance