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 , I WANT TO SEPARATE A SIX DIGIT NUMBER INTO INDIVIDUAL DIGIT IN SQL SERVER…..FOR EXAMPLE…123456 AS 1 2 3 4 5 AND 6.
This is one of the methods
declare @n int
set @n=123456
select stuff(stuff(stuff(stuff(stuff(@n,2,0,’ ‘),4,0,’ ‘),6,0,’ ‘),8,0,’ ‘),10,0,’ ‘)
Which SQL function ll use to convert 2567 in the form of “two thousand five hundred sixty seven” ?
Hi Need a Solution Like
Student_Table:
ID(bigint) Name(varchar) SubjectID(varchar)
1 x1 1,2
2 x2 2,3
3 x3 2,3
Subject_table
ID(bigint) Name(varchar)
1 S1
2 S2
3 S3
I Need a result LIke
ID Name SubjecyName
1 x1 S1,S2
2 x2 S2,S3
3 x3 S2,S3
How Can i get the above result….?
Regards
Naag
hi,
i want to conver string as ‘2011 11’ into a number like 201111.
how can i do that?
10’x!
Anna
Hi,
Try the below code:
DECLARE @yrmth VARCHAR(15)=’2011 11′
SET @yrmth=REPLACE(@yrmth,’ ‘,’.’)
SELECT CAST(PARSENAME(@yrmth ,2)+PARSENAME(@yrmth,1) AS INT)
select replace(‘2011 11′,’ ‘,”)*1
I want to cast a varchar and only take the numeric characters and make an int.
Is this posible?
Sorry the bad writing, im spanish.
You [two words removed]
Explicit conversion from data type text to int is not allowed.
hello I need some problem on following code:
SELECT REP,A.DEALER,A.PROD AS PRODUCT,OPENING,PRI,SEC,PRI-SEC+OPENING AS CLOSING,PRI_AMT ,SEC_AMT FROM (
SELECT VNO,DATE_T AS DATESAL,ACCOUNT AS DEALER,PRODUCT AS PROD,SUM (convert(int,QUANTITY))
AS PRI,NET_AMOUNT AS PRI_AMT FROM SALES_DETAILS WHERE QUANTITY’ ‘ GROUP BY ACCOUNT,PRODUCT,DATE_T,NET_AMOUNT,VNO) A, (
SELECT REPRESENTATIVE_NAME AS REP,DATE_SALES AS DATESAL,DEALER_NAME
AS DEALER,PRODUCT_NAME AS PROD,SUM(convert(int,OPENING_STOCK))AS
OPENING,SUM(convert(int,SECONDARY_SALES))AS SEC,SUM(convert(int,SECONDARY_SALES) )*RATE
AS SEC_AMT FROM SALES_ENTRY_DETAILS GROUP BY
PRODUCT_NAME,DATE_SALES,DEALER_NAME,REPRESENTATIVE_NAME,RATE) B
WHERE A.DATESAL=B.DATESAL AND A.DEALER=B.DEALER
AND A.PROD=B.PROD AND CONVERT(date,B.DATESAL ,103) BETWEEN CONVERT(date,’10-10-2010′,103)
AND CONVERT(date,’10-10-2012’,103)
In the above coding PRI – SEC + OPENING occurs error
Hello I need some help
I’m trying to retreive the Min and Max value of a field that contain the following, fractions numbers and Text.
Name Finding Name Value
Jason First Test Pass
Jason Book BP 113/67
jason Book BP 115/80
Jason Book BP 160/80
Jason Book Temp 100
Jason Book Temp 99
Jason Book Temp 95
When I use the following code MAX(value)
I receive:
Jason First Test Pass
Jason Book BP 113/67
Jason Book Temp 99 this number should be 100 since 100 is the MAX value in Book Temp filed.
Can someone help me
Hi I am using SQL server 2008, I have a problem in retrieving numeric values from the below data.
problem
———–
1
1 – Critical
2
2 – High
3
3 – Medium
4
4 – Low
5
———-
From the above values of Problem field, I want to retrieve only numeric values and numeric part of the values. please help me with the suitable query ASAP.
Thanks in advance
select substring(problem,1,patindex(‘%[^0-9]%’)-1) from table
Hi,
I have a varchar value like this ‘00001342470’
How can I get rid of all the zeros (only at the begining) to show it like this
‘1342470’?
Thank you in advance
select cast(col as int) as col from table
hi
i need a style format value for MM/dd/yyyy hh:mm:ss AM
For eg like this 02/05/2012 12:20:23 AM
Nithin
Either use a convert function or do it in front end application
ALTER PROCEDURE Proc_Filter_Mobile
— Add the parameters for the stored procedure here
@MOBSER_ID SMALLINT
AS
BEGIN
DECLARE @INT_POS SMALLINT,
@ANI NVARCHAR(20)=NULL,
@CUR_ANI NVARCHAR(1)=NULL,
@FLAG TINYINT,
@ANI_ID INT,
@ANI_SUB NVARCHAR(6)=NULL,
@COUNT TINYINT=0;
DECLARE @CUR_FILTER CURSOR;
SET NOCOUNT ON;
DECLARE @CHK_ANI TINYINT;
CREATE TABLE #Bulk_Load_Mobile_Series
(
Load_ID BIGINT IDENTITY(1,1),
MobileSeries_SeriesID int,
MobileSeries_ANI nvarchar(20)
)
— ## SETTING CURSOR ##
SET @CUR_FILTER=CURSOR FOR
SELECT DataTemp_ID,DataTemp_ANI FROM Bulk_DataTemp;
— ## OPENING CURSOR
OPEN @CUR_FILTER
–## FETCHING CURSOR VALUE
FETCH @CUR_FILTER INTO @ANI_ID,@ANI
WHILE @@FETCH_STATUS=0
BEGIN
–## CUTTING LAST FIVE CHARACTER FROM ANI
SET @ANI_SUB=SUBSTRING(@ANI,9,5);
–## CHECKING GOLDEN,SILVER AND PLATINUM NUMBER AND FILTERED
SET @INT_POS=0;
WHILE @INT_POS<=9
BEGIN
SET @CHK_ANI=1;
SET @FLAG=0;
WHILE @CHK_ANI=3)
BEGIN
SET @COUNT=1;
END;
–PRINT ‘CURRENT ANI -‘ + @CUR_ANI;
–PRINT CAST(@INT_POS AS VARCHAR);
— PRINT ‘FLAG VALUE’ + CAST(@FLAG AS VARCHAR);
END;
ELSE
BEGIN
SET @FLAG=0;
END
SET @CHK_ANI=@CHK_ANI+1;
END
SET @INT_POS=@INT_POS+1;
END
IF(@COUNT1)
BEGIN
–### DELETE THAT RECROD FROM THE TABLE###
— PRINT @ANI_ID;
INSERT INTO #Bulk_Load_Mobile_Series(MobileSeries_SeriesID,MobileSeries_ANI)
VALUES(@MOBSER_ID,@ANI);
–DELETE FROM [DBO].Bulk_DataTemp
–WHERE DataTemp_ID=@ANI_ID;
SET @FLAG=0;
SET @COUNT=0;
END
ELSE
BEGIN
SET @COUNT=0;
SET @FLAG=0;
END;
FETCH @CUR_FILTER INTO @ANI_ID,@ANI
END
CLOSE @CUR_FILTER;
DEALLOCATE @CUR_FILTER;
SELECT * FROM #Bulk_Load_Mobile_Series
END
GO
I have a doubt — I am moving data from SQL to ORACLE DW and The column in SQL has char(23) and I am using substring of this CTRLNUM to get SOME ID and moving into Data-Warehouse.
How should I convert this char into Number to load into Data warehouse.
I am using something like this —
Convert (int,SUBSTRING(CTRLNUM,3,10)) AS LIMID..
It says — Conversion failed – varchar to data-type-int
It means that it has some bad data that cannot be converted to INT. First do a select statement and see what it returns
Hi all,
I am using Sybase 12.5 and not SQL and not sure if anyone can help.
Anyway the question is:
I have a table with a column that has customer names and branch names. Branch names start with number (less than 1000) and need to know if I can select only the branches from the table. The problem is Sybase 12.5 does not have functions. Functions came into existence only in v15.x
My statement is:
SELECT * FROM Customers WHERE CONVERT(INT, LEFT(Name,3)) < 1000
This throws up an error when it encounters the first record that's not starting with number.
Any help would be much appreciated.
Nissar
Hi All
I have a table with a column Zip cod (varchar), and I want to filter data based on condition (which would be numeric)
Something like this:
Select * From Address WHERE (CONVERT(INTEGER, Address.ZipCode) >= 1000 AND CONVERT(INTEGER, Address.ZipCode) <= 5000)
AND ISNUMERIC(Address.ZipCode) = 1
but the same is throwing error as we have values like '-', '50-45252' in the DB column.
Error: Conversion failed when converting the varchar value '.' to data type int
Pl. help me to solve this.
Hi All
I need a small help
———–
I have a nvarchar value something like ‘1,3,5,6’, in my database table
But I want to use this in a where condition, something like this where event_id IN (1,3,5,6)
How do I achieve that, How can I convert it and use in the IN of a query
————-
Are you passing values via parameter?
I have the following script. Everthing works EXCEPT the last 2 lines regarding Voter.PARITY. I think I need to convert A.ADDR_HN to an integer again but it isn’t working when I enter the CAST function as similarly done in the previous 2 lines regarding Voter.MIN/MAX_RNG_3. Can anyone help?
— ===========================================
— Set Flag on addresses that are in address range…
— ==========================================
UPDATE gis_prod.SDE.TMP_ADDRPT_FOR_VOTER_LOCATORS –AddressPoint
SET TMP_IN_RNG_FLG = 1,
TMP_IN_RNG_OID = Voter.OBJECTID,
— TMP_ADCO_VALFLG_CL = Voter.VAL_FLG,
TMP_PRECINCT_CL = Voter.PRECINCT
FROM
gis_prod.SDE.TMP_ADDRPT_FOR_VOTER_LOCATORS A –AddressPoint A
INNER JOIN gis_prod.sde.TMP_ADCO_VoterLocator_City Voter
ON
A.ADDR_ALPHA = Voter.ADDR_ALPHA AND
(
cast (A.ADDR_HN as int) >= Voter.MIN_RNG_3 and
cast (A.ADDR_HN as int) <= Voter.MAX_RNG_3
)
AND
(
(A.ADDR_HN % 2 = 0 AND Voter.PARITY = 'EVEN') or
(A.ADDR_HN % 2 = 1 AND Voter.PARITY = 'ODD')
)
GO
I have the data like 1333200.3900 in a nvarchar column. I need to convert it into numeric…
Hi guys,
I am creating web form insert record to sqlserver, But i get a trouble like this: error: converting data type varchar to float. For handle it I try to using cast and convert. But until right now i still get trouble Incorrect syntax near ‘textbox1’. Any one can help me? Thanks.
=========================================
Dim typlat As Double = txtLat.Text
Dim typlot As Double = txtLon.Text
Dim adl As String
adl = CSng(Val(typlat))
adl = CSng(Val(typlot))
strSQL = “SELECT CAST(Latitude as float) from DBREPORTCUSTOM”
strSQL = “SELECT CONVERT(Double ‘” & typlat & “‘ ‘” & typlot & “‘) from DBREPORTCUSTOM”
===============================================