July 8, 2015 at 9:30 am
Minnu (7/8/2015)
Numeric values in the first query should be converted to INTEGER.
You can't mix character data and integer data in the same column.
What are you trying to accomplish? Based on the sample data you posted, what is the expected output?
July 8, 2015 at 9:39 am
Lynn Pettis (7/8/2015)
Michael L John (7/8/2015)
Try using a UNION ALL.Won't work since the OP wants the numeric values converted to integer values:
select cast(Data as int) from #TestData where Data not like '%[^0-9]%'
union all
select Data from #TestData where Data like '%[^0-9]%';
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'A1' to data type int.
Duh. Same with a CASE statement.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 8, 2015 at 9:53 am
Lynn Pettis (7/8/2015)
Minnu (7/8/2015)
Numeric values in the first query should be converted to INTEGER.
You can't mix character data and integer data in the same column.
What are you trying to accomplish? Based on the sample data you posted, what is the expected output?
Before we can continue, we really need to know the answers to both of these questions. Both are important in order to give you the proper assistance.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 8, 2015 at 11:11 am
Minnu (7/8/2015)
Numeric values in the first query should be converted to INTEGER.
To be displayed or handled by what? If you query the table in SSMS then copy the results to Excel, the numeric values will be treated (by Excel) as numbers, the others as text.
Don Simpson
July 13, 2015 at 8:10 am
As stated we do need more information on your goal. However, typically the only reason I have seen to worry about making numeric data in mixed varchar sets int was to ensure sort order of those items
So instead of alpha sort like this
1
100
11
2
200
21
You get numeric sort like this
1
11
100
2
21
200
If that is the case then you can do this in your order by clause but it takes a good deal of work. If you want to simply sort all numeric values numerically and those with alpha can be sorted alpha sorting then this can be done this way
create table #TestData (Data varchar(5));
insert into #TestData
SELECT
Data
FROM (VALUES
(CAST('1' AS VARCHAR(5))),('11'),('2'),('A1'),('A10'),('A11'),('246'),('AB1'),
('AB10'),('100'),('256'),('B1'),('B2'),('124'),('20'),('B21'),('B31'),('32'),('68')
) d (Data);
SELECT * FROM #TestData ORDER BY
(CASE WHEN IsNumeric(Data) =1 THEN 0 ELSE 1 END),
(CASE WHEN IsNumeric(Data) =1 THEN CAST(Data AS Int) ELSE 0 END)
However if you need to wort alpha values with numbers numerically within the alpha sort it is a bit more convoluted
/* Note I only work through letter J in this example and I do not account for any extraneous characters such as !@#$%^ etc. */
SELECT * FROM #TestData ORDER BY
(CASE WHEN IsNumeric(Data) =1 THEN 0 ELSE 1 END),
(CASE WHEN IsNumeric(Data) =1 THEN CAST(Data AS Int) ELSE 0 END),
(CASE WHEN IsNumeric(Data) != 1 THEN CAST(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Data,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') AS Varchar(10)) ELSE '' END),
(CASE WHEN IsNumeric(Data) != 1 THEN CAST(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Data,'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J','') AS INT) ELSE '' END)
Of course depending on the version of SQL you are using you can wrap some of the logic in a UDF.
July 14, 2015 at 12:42 pm
Use Try_Convert, which does what IsNumeric should have done... Try_Convert(<type>, <data>) returns a NULL if the data cannot be converted to the type, or returns the data converted to the type, so...
create table MyTable ( [data] varchar(255) );
...
-- list numeric values in order
select try_convert(int, [data])
from MyTable
where try_convert(int, [data]) is not null
order by 1;
-- list non-numeric values in order
select isnull(try_convert(int, [data]), [data])
from MyTable
where try_convert(int, [data]) is null
order by 1;
Try_Convert is your friend. It has simplified a LOT of handling of columns with mixed data types for me.
One minor caveat... Try_Convert will return a zero for a source data value of "-" (just a hyphen/dash). Effectively, a single hyphen/dash character is treated as a "negative zero" value, which returns zero instead of null. So - if you might hit that value, and don't want that interpreted as a zero, you would need to explicitly handle that particular value. Otherwise, Try_Convert works as expected.
July 14, 2015 at 2:18 pm
Regarding simply ordering the values so that numerics are all first - in numeric order, and alphas follow in alpha order, that is also simple with the Try_Convert method.
select [data]
from mytable
order by
isnull( try_convert( int, [data] ), 2147483647 )
,[data]
Try_Convert will return the numeric values for the true numbers, and NULL for the non-numeric values, so Isnull will return the actual numeric values for all numerics, and the max int value for all non-numerics. Then referencing the original [data] value as the second order by value will get those alphas in the correct order as well.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply