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 a problem to convert from varchar to Numeric.
Select
Convert(Numeric,ltrim(rtrim(Replace(Replace(BankAccountNo,Char(13),”),Char(10),”)))) As AccNo
From
ReceiptPayment
Where
isnumeric(ltrim(rtrim(Replace(Replace(BankAccountNo,Char(13),”),Char(10),”))))=1 And
BankAccountNo Is Not NULL
And BankAccountNo ”
is my query. I am getting ‘Arithmetic overflow error converting varchar to data type numeric’ (retrieved only partial records with error).
But If I retrieve all records by placing ‘*’ instead of convert statement. What will be the solution?
hi, i have a column that it must be numeric because i use the function MAX somewhere with it , for example,
ADOQuery1->SQL->Text =”SELECT MAX (code) AS NextCode FROM product “;
but i use it also sumewhere else and i compare it with an EditBox text something like this for example
ADOQuery1->SQL->Text=
“SELECT ProductName FROM product WHERE product.code= ‘”+Edit1->Text+”‘ ” ;
in the second example it throws a mistake because i compare an integer with a string i tried this
ADOQuery1->SQL->Text=
“SELECT ProductName FROM product WHERE product.code= ‘StrToInt(Edit1->Text)’ ” ;
but it dosen’t seem to work can you telm if what i am trying to do can be done with CAST or CONVERT and how??????
(sry for my bad english)
i thing it must be something like this for example
“SELECT MAX ( SELECT CONVERT(INT, code) FROM customer) AS PLITHOS FROM customer “;
Hello Xristina,
It seems you are writing the below query in .Net editor:
ADOQuery1->SQL->Text=
“SELECT ProductName FROM product WHERE product.code= ‘StrToInt(Edit1->Text)’ ” ;
Form this query remove the single quote and write as below:
ADOQuery1->SQL->Text=
“SELECT ProductName FROM product WHERE product.code= ” + StrToInt(Edit1->Text) ;
Regards
Pinal Dave
thnks a lot pinal it works perfect…..
Hello.
Can you please tell me the following?
I Have text like ’16pages brochure 4colours’ and in need to convert this text to a unique Alphanumeric numeric Code
Thank you in advanced
Can you show us your expected result?
Give me the output data Nick
Sorry i mean
a unique Alphanumeric OR numeric Code
For the above sample data what is your expected result?
I am using Sql 2005
I have a .xls sheet of nubmers (6000+ of them) and they are straight text.
I ahve tried the phone convert but can’t seem to get it to work.
I need to pull them formatted into a varchar(256) column. they come like this:
000000000000000000000000000000000000
and I need them inserted into the table like this:
00000000-0000-0000-0000-0000-000000000000
They are always the same legnth and they will only have ‘-‘ between them.
Thanks,
Jr
Thanks for the help.
I’m hoping you can help me.
I have the following expression:
Select power(power(10.000000000,3.03259784139734),28664879.2888658)
I have tried to execute this expression in SqlServer 2005 but I still get the error message in below.
Arithmetic overflow error converting expression to data type float.
Do you have any idea what I’m doing wrong?
@Thiru
The resultant number is too high.
For example, try: SELECT POWER(1000, 4)
The return is INT< and that is too high for INT, so it produces an error.
Change it to: SELECT POWER(1000.0, 4)
and it works, as the return type is FLOAT.
But the same thing happens with FLOAT, if the number is too high, it will also produce an error.
Works: SELECT POWER(1000.0, 12)
Does not work: SELECT POWER(1000.0, 13)
can any one tel me difference between convert and cast
@chaitanya
CONVERT does more than CAST. CAST is an old standard, so many people like it.
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
hi swami,
i am building a database and in a table my primarykey is of the type char(4) and my need is to autoincrement it and i wish to have the details like ‘E001’ and then increment it. is it possible?
Regards,
Anu
Probably easiest way is to write procedure that does inserting.
1. Make sure procedure is ran inside transaction or start one
2. Get the largest PK value from the table (or store it somewhere else)
3. Insert a new row with largest value + 1
4. Commit transaction if you started one in the first step
yes its possible I have done it BG just see the code and make necessary arrangements…
Hello Sir
I am shredding some xml into relational SQL 2005 and the source xml has date time fields in strings of ISO 8601 format. I wish to change these to SQL DateTime fields during shred. I have tried things like
SELECT ID, CAST( LASTMODTIME AS DATETIME)
FROM PROFILES
but the conversion fails.
Hi Andy,
Can YOU Brief out with representation of Data…….. that means how you xml data
Hi everyone, I have a question on sorting a varchar field in numeric order. It is a database we use for testcases.
Say I have a varchar field “reference” with these data:
1.1.1
1.10.1
4.11.1
4.12.1
1.2.1
1.3.1
So if I simply do “order by reference”, i’ll get:
1.1.1
1.10.1
1.11.1
1.2.1
1.3.1
While the correct order should be
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
4.11.1
4.12.1
I know I can probably use CONVERT, but it will error out at the dot value “.”.
Any suggestions?
Conversion failed when converting the nvarchar value ‘1.1.1’ to data type int.
Thanks!
Alex
Here’s one way to do it.
— CTE to split version number field into parts
— How I would love to have regular expressions!
WITH CTE(Field1, Field2, Field3)
AS
(
SELECT
LEFT(Reference, PATINDEX(‘%.%’, Reference) – 1),
SUBSTRING(Reference, PATINDEX(‘%.%’, Reference) + 1, PATINDEX(‘%.[0-9]’, Reference) – PATINDEX(‘%.%’, Reference) – 1),
SUBSTRING(Reference, PATINDEX(‘%.[0-9]’, Reference) + 1, LEN(Reference))
FROM
MyTable
)
SELECT
— Concatenate fields back to single line
Field1 + ‘.’ + Field2 + ‘.’ + Field3
FROM CTE
— Fields must be cast to integer before ordering them
— Otherwise DB performs natural sort which doesn’t
— work very well in this case
ORDER BY CAST(Field1 AS INT), CAST(Field2 AS INT), CAST(Field3 AS INT)
@Alex
If none of the sub-parts ever have leading zeroes, just replace the dots, add a 1, and cast as a number.
replace the dots, so there is one big number.
add a one, to keep any final 0s
cast as a number to have the order work:
WITH
Data(Reference)
AS
(
SELECT ‘1.1.1’ UNION ALL
SELECT ‘1.10.1’ UNION ALL
SELECT ‘4.11.1’ UNION ALL
SELECT ‘4.12.1’ UNION ALL
SELECT ‘1.2.1’ UNION ALL
SELECT ‘1.3.1’
)
SELECT
Reference
FROM
Data
ORDER BY
CAST(REPLACE(Reference, ‘.’, ”) + ‘1’ AS BIGINT);
Ideally, a three-part code like this should be in three different COLUMNs. That would remove any such issues as the disparate parts are kept separately.
Use this query
select pointdt from pointsrt order by cast(Replace(pointdt,’.’,”) as int) asc
Data Like this
1.1.1
1.10.1
4.11.1
4.12.1
1.2.1
1.3.1
Out put like this
1.1.1
1.2.1
1.3.1
1.10.1
4.11.1
4.12.1
hi sir,
this site is very useful for me..
i have 1 doubt.can u pls clear my doubt.
i have money datatype values like
price=650.0000000
price=755.6666666
but i want 2 display like
price=650.0000
price=755.6667
i tried with round(price,4)..
but it doesnt work..
pls help me sir,
regards,
pushpa.
CAST as MONEY might work for you. Try this:
SELECT CAST(650.0000000 AS MONEY), CAST(755.6666666 AS MONEY)
SELECT CAST(650.0000000 AS decimal(18,4))
SELECT CAST(755.6666666 AS decimal(18,4))
hi dave,
thanks for ur reply…
for the last 4 months i m learning sql..i learned most of the topics in theoretical part.
but i want to practice all the topics through websites..can u please suggest any website to practice sql queries..
i m looking the website which will have table structures and query with answers..i m using toad tool for practice the sql queries.
once again thanks ….
Regards,
pushpa
I need the code to work out a persons age from his given birthdate till the current (maby sysdatetime()).
Im trying convertion from date to int, but it giving me trouble.
How would I do this?
Hello Minnaar,
Use the DATEDIFF function to get the difference in years, months or days.
Regards,
Pinal Dave
HI All,
I have one sales column in that vales are like this
200
345.00
56.00
667
5
At present my sales column datatype is nvarchar
and i wanted my sales column data should be in the below format can u please help me out …
$200
$345.00
$56.00
$667
$5
Thanks& Regards,
Dileep.v
Sorry i won’t think it as Big problem But if ur not able to get this i may think ur fresher. r fooling some one………
select ‘$’+ moneys from Dileep
Data like this
200
345.00
56.00
667.00
5.00
Out put data like this
$200
$345.00
$56.00
$667.00
$5.00
Sorry i won’t think it was a Big problem But if ur not able to get this i may think ur fresher. r fooling some one………
select ‘$’+ moneys from Dileep
Data like this
200
345.00
56.00
667.00
5.00
Out put data like this
$200
$345.00
$56.00
$667.00
$5.00
Hi Bob and others Use this query But restrictions should be done By you .
ALTER Function durationtime(@firstColumn varchar(45) ,@secondColumn as varchar(45)) returns varchar(500)
as
begin
declare @fidurationpt1 varchar(45)
declare @fidurationpt2 varchar(45)
declare @fidurationpt3 varchar(45)
declare @secdurationpt1 varchar(45)
declare @secdurationpt2 varchar(45)
declare @secdurationpt3 varchar(45)
declare @secduration varchar(60)
declare @totduratpd varchar(505)
declare @duratpt1 int
declare @duratpt2 int
declare @duratpt3 int
declare @firslen int
declare @seclen int
set @firslen =len(@firstColumn)
set @seclen = len(@secondColumn)
if @firslen < 6
Begin
set @firstcolumn ='0' + @firstColumn
set @fidurationpt1 =substring(@firstColumn,1,2)
set @fidurationpt2 =substring(@firstColumn,3,2)
set @fidurationpt3 =substring(@firstColumn,5,2)
End
Else
set @firstcolumn = @firstColumn
set @fidurationpt1 =substring(@firstColumn,1,2)
set @fidurationpt2 =substring(@firstColumn,3,2)
set @fidurationpt3 =substring(@firstColumn,5,2)
if @seclen < 6
Begin
set @secondColumn ='0' + @secondColumn
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
End
Else
set @secondColumn = @secondColumn
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
set @duratpt1 = cast(@secdurationpt1 as int)- cast(@fidurationpt1 as int)
set @duratpt2 = cast(@secdurationpt2 as int)- cast(@fidurationpt2 as int)
if @duratpt2 < 0
Begin
set @duratpt1 = @duratpt1-1
set @duratpt2 = 60 – @fidurationpt2 + @secdurationpt2
End
else
set @duratpt2=@duratpt2
set @duratpt3 = cast(@secdurationpt3 as int)- cast(@fidurationpt3 as int)
set @totduratpd =cast(@duratpt1 as varchar(45))+ 'hrs:' + Replace(cast(@duratpt2 as varchar(45)),'-','')+ 'min:' + replace(cast(@duratpt3 as varchar(45)),'-','')+'sec'
–set @totduratpd = cast(@duratpt1 as varchar(135))
return @totduratpd
End
insert into serverlogon(Logontim,Logofftim)values(153500,173000)
select dbo.durationtime(cast(Logontim as varchar(45)),cast(Logofftim as varchar(45))) as durationtime from serverlogon
Logon
160138
130138
120138
120138
201500
211500
81500
163000
153500
Logoff
180348
180348
190348
190137
221000
221000
91000
201000
173000
Out put
2hrs:2min:10sec
5hrs:2min:10sec
7hrs:2min:10sec
7hrs:0min:1sec
1hrs:55min:0sec
0hrs:55min:0sec
0hrs:55min:0sec
3hrs:40min:0sec
1hrs:55min:0sec
Put validations as Logon time always less than Logoff time….either by creating rule or in this code
ALTER Function durationtime(@firstColumn varchar(45) ,@secondColumn as varchar(45)) returns varchar(500)
as
begin
declare @fidurationpt1 varchar(45)
declare @fidurationpt2 varchar(45)
declare @fidurationpt3 varchar(45)
declare @secdurationpt1 varchar(45)
declare @secdurationpt2 varchar(45)
declare @secdurationpt3 varchar(45)
declare @secduration varchar(60)
declare @totduratpd varchar(505)
declare @duratpt1 int
declare @duratpt2 int
declare @duratpt3 int
declare @firslen int
declare @seclen int
set @firslen =len(@firstColumn)
set @seclen = len(@secondColumn)
if @firslen < 6
Begin
set @firstcolumn ='0' + @firstColumn
set @fidurationpt1 =substring(@firstColumn,1,2)
set @fidurationpt2 =substring(@firstColumn,3,2)
set @fidurationpt3 =substring(@firstColumn,5,2)
End
Else
set @firstcolumn = @firstColumn
set @fidurationpt1 =substring(@firstColumn,1,2)
set @fidurationpt2 =substring(@firstColumn,3,2)
set @fidurationpt3 =substring(@firstColumn,5,2)
if @seclen < 6
Begin
set @secondColumn ='0' + @secondColumn
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
End
Else
set @secondColumn = @secondColumn
set @secdurationpt1 =substring(@secondColumn,1,2)
set @secdurationpt2 =substring(@secondColumn,3,2)
set @secdurationpt3 =substring(@secondColumn,5,2)
set @duratpt1 = cast(@secdurationpt1 as int)- cast(@fidurationpt1 as int)
set @duratpt2 = cast(@secdurationpt2 as int)- cast(@fidurationpt2 as int)
if @duratpt2 < 0
Begin
set @duratpt1 = @duratpt1-1
set @duratpt2 = 60 – @fidurationpt2 + @secdurationpt2
End
else
set @duratpt2=@duratpt2
set @duratpt3 = cast(@secdurationpt3 as int)- cast(@fidurationpt3 as int)
if @duratpt3 < 0
Begin
set @duratpt2 = @duratpt2-1
set @duratpt3 = 60 – @fidurationpt3 + @secdurationpt3
End
set @totduratpd =cast(@duratpt1 as varchar(45))+ 'hrs:' + Replace(cast(@duratpt2 as varchar(45)),'-','')+ 'min:' + replace(cast(@duratpt3 as varchar(45)),'-','')+'sec'
–set @totduratpd = cast(@duratpt1 as varchar(135))
return @totduratpd
End
select dbo.durationtime(cast(Logontim as varchar(45)),cast(Logofftim as varchar(45))) as durationtime from serverlogon
Logon
160138
130138
120138
120138
201500
211500
81500
163000
153500
153539
Logoff
180348
180348
190348
190137
221000
221000
91000
201000
173000
163438
Duration Time
2hrs:2min:10sec
5hrs:2min:10sec
7hrs:2min:10sec
7hrs:1min:59sec
1hrs:55min:0sec
0hrs:55min:0sec
0hrs:55min:0sec
3hrs:40min:0sec
1hrs:55min:0sec
0hrs:58min:59sec
Hi,
I have a question. what i have to do when i want a string in ‘00001’ format when this is a auto-generated code?
I want to select the max(id) from the table and format the int to my required format like ‘00001’?
Please help. Am waiting.
Hi sinha can u brief You want i think that you need to generate Increament which is in string type.is that your question Suppose you say ‘000001’ you need auto increament and you query it ‘000002’ should be produced.is that ur question
Hi sinha can u brief what You want. i think you need to generate an Increament value which is of string type is that your question? Suppose you say ‘000001′ you need to auto increament (i.e you query it ‘000002′) should be produced.Is n’t it?
Have an identity column to generate number
Have a computed colum as right(‘000000’+cast(id_col as varchar(10)),6)