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
i have an integer 24036 in mssql 2000, i need to convert it to month and year.(no date).
Please give me the solution
What is your expected result?
Hi,
I have used to this query,.
declare @v varchar(50)
set @v=’-10851776011.372′
select cast(cast(@v as float) as DECIMAL(20,14)) as Value1
The error it will come like,
Arithmetic overflow error converting float to data type numeric.
How can i convert this type of values,,
Help me please…
@Maniraj
DECIMAL(20, 14) allows for 6 digits to the left of the decimal point. Your number has 11. Hence the overflow.
Perhaps you mean to use DECIMAL(20, 6), which allows 14 to the left and only 6 on the right.
Hi All,
I have a table with column chapterid as int
stored procedure
@chapterid int,
SELECT * FROM Chapters WHERE Chapter IN (@chapterid)
@chapter has multiple values like @chapterid = ‘5,6,7,8’
this gives me cast error
how do I convert this varchar to an int? A simple convert or cast statement doesn’t work. Any help would be much
appreciated.
Search for Array+SQL Server in google. You will get to know lot of methods
@Shalini
To search for a list like that, you can use Dynamic SQL.
sir i have a question:
i hav given a single number say 138 ok
and i have to display only 8 which is largest in this given number using sql
plz help me as early as possible
@ammupriya
WITH
DATA(Datum)
AS
(SELECT 138),
Separated(Datum, i, Amount, Digit)
AS
(
SELECT
Datum,
1,
LEN(Datum),
SUBSTRING(CAST(Datum AS VARCHAR), 1, 1)
FROM
Data
UNION ALL
SELECT
Datum,
i + 1,
Amount,
SUBSTRING(CAST(Datum AS VARCHAR), i, 1)
FROM
Separated
WHERE
i <= Amount
)
SELECT
MAX(Digit)
FROM
Separated;
hi how to select only month from string format like (01/06/2009) to June pls help..
Hello Parthee,
Use the below statement:
SELECT DATENAME(MONTH,CONVERT(DATETIME,’01/06/2009′,103))
Kind Regards,
Pinal Dave
Hi everyone, I have a question on sorting a varchar field in numeric order. It is a database we use for engineers, so usually it contains numbers, but sometimes it contains characters as well, and it explains why we used varchar to start with
Say I have a varchar field “frequency” with these data:
1.0
3
4.5
–
100
35
So if I simply do “order by frequency”, we’ll get
–
1.0
100
3
35
4.5
While the correct order should be
–
1.0
3
4.5
35
100
I know I can probably use CONVERT, but it will error out at the dash value “-“. Any suggestions?
Thanks!
Justin
Use this Function
Hi Justin and who get this problem…….
alter function sort(@Column varchar(30)) returns varchar(30)
as
Begin
Declare @Sfindex int
Declare @Beginse varchar(35)
set @Sfindex = patindex(‘%.%’,@Column)
if @Sfindex = 0
Begin
set @Beginse =@Column +’.0′
End
else
set @Beginse =@Column
return @Beginse
end
Use this query…….
select sotcolumn from sortdata order by cast(Replace(dbo.sort(sotcolumn),’.’,”) as int) asc
Data.
1.0
3
4.5
–
100
35
Out put
–
1.0
3
4.5
35
100
Try this
select data from
(
select cast(‘1.0’ as varchar(10)) as data union all
select ‘3’ union all
select ‘4.5’ union all
select ‘-‘ union all
select ‘100’ union all
select ’35’
) as t
where data not like ‘%[^0-9.]%’
order by cast(data as numeric(12,2))
Hello Justin,
You can do it by using CTE like below:
with cte (frequency,frqOrderBy)
as
(select frequency, case isnumeric(frequency) when 1 then convert(money, frequency) else 0 end as frqOrderBy from tblTable )
select frequency from cte order by frqOrderBy
Kind Regards,
Pinal Dave
Thanks! I’ll give it a try.
Justin
I have a problem to export data from excel using opendatasource. I have one column in excel that contains both integers in some rows & text in some rows . When i start importing them text data comes as it is but records containing integer data are being imported but with null values into that column….
Any help will be highly appreciable….
Format the cell to have character data
Try using nvarchar column in the table
Hi, I have currency conversions which are converting from data type money to numeric(38,6) which is in turn throwing an error in my BI tool. I know how to solve that problem, but along the way I became interested to be able to determine the number of digits in stored value. I was actually curious to do some analysis to see if there was a pattern to the currency conversions so I might could change the scale when loaded certain currencies. Thank You
Pinal,
I just wanted to say thanks. I find your blog to be very useful and infomative.
Dan
I want to convert a 4-digit number to dateTime SQL Server 2005. i.e. 9325, the first number is year, so it is 2009, and the rest of number is days of year, so it will be 325th days of 365. How do I do that?
Thank you.
@Lydia
The year is the thousands + 100 (because date 0 in SQL Server is year 1900). To get the thousands, divide by 1000.
The day of year is the remainder of that same division, so % 1000 – 1.
Put those together in DATEADD and you have a date.
WITH Data(X) AS (SELECT 9325)
SELECT DATEADD(y, X % 1000 – 1, DATEADD(yy, 100 + X / 1000, 0)) FROM Data;
Anoher method
declare @date int
select @date=9325
select dateadd(day,@date%1000-1,dateadd(year,2000+@date/1000-1900,0))
Thank you VERY MUCH !!! It works VERY WELL !!!
Hi,
I have a view where I used to convert a numeric column to numeric(32,9). But now I have to convert the same column to numeric but the decimal places aer not fixed but should be taken from a column in table.
Please let me know if there is any way I can use a column or a variable in the numeric() function something like convert(numeric(32,table.dec_places). Please help.
There is no way other than using a dynamic sql
Hello,
I have a table with 5000 IDs. from ID 1-5000. I want to change the ID 1 to 2500, for ex. 1=2500, 2= 2501, 3= 2502….etc.
Hello Subhendu,
At first you will have to remove the IDENTITY property from ID column, then update the ID values and then again add the IDENTITY property.
Regards,
Pinal Dave
Hi Subhendu,
1. If column is IDENTITY then remove IDENTITY for time being.
2. Update data as:
UPDATE Table
SET ID = ID + 2499 –(As you need to start from 2500)
3. If column was IDENTITY column, then mark as IDENTITY now.
Thanks,
Tejas
SQLYoga.com