July 8, 2015 at 6:41 am
Hi Team,
I have a table with column "Data" as VARCHAR, with entries like below.
1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68
i want to select the data by converting varchar to int for numberic values and for alphanumeric it should display as it is.
SELECT CAST(dataAS INT) FROM record_tab
getting below error
Conversion failed when converting the varchar value 'A1'
July 8, 2015 at 7:09 am
Minnu (7/8/2015)
Hi Team,I have a table with column "Data" as VARCHAR, with entries like below.
1
11
2
A1
A10
A11
246
AB1
AB10
100
256
B1
B2
124
20
B21
B31
32
68
i want to select the data by converting varchar to int for numberic values and for alphanumeric it should display as it is.
SELECT CAST(dataAS INT) FROM record_tab
getting below error
Conversion failed when converting the varchar value 'A1'
Is this for sorting your output?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 8, 2015 at 7:09 am
When using CAST /CONVERT you cannot convert a field to a different data type if not all the values can actually be converted or are compatible. All the INT values can be converted to VARCHAR but not all the VARCHAR can be converted to INT. Like in your example 'A1' cannot be converted to INT. there is no way to convert 'A' to a numeric value.
Can you provide a scenario or example why you would want to convert the datatype to INT in this case.
July 8, 2015 at 7:15 am
@sqletl (7/8/2015)
When using CAST /CONVERT you cannot convert a field to a different data type if not all the values can actually be converted or are compatible. All the INT values can be converted to VARCHAR but not all the VARCHAR can be converted to INT. Like in your example 'A1' cannot be converted to INT. there is no way to convert 'A' to a numeric value.Can you provide a scenario or example why you would want to convert the datatype to INT in this case.
For sorting, perhaps:
SELECT
Data,
RIGHT('0000'+Data,4)
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)
ORDER BY RIGHT('0000'+Data,4)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 8, 2015 at 8:37 am
Hi Team,
Want to execute a single query to fetch the numberic and alphanumeric values with following two queries
select cast(data as integer) from record_tab where ISNUMERIC(data) = 1
select data from record_tab where ISNUMERIC(data) <> 1
July 8, 2015 at 8:55 am
You want something like this:
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;
select cast(Data as int) from #TestData where Data not like '%[^0-9]%';
select Data from #TestData where Data like '%[^0-9]%';
You aren't going to get this in one query if you want all numeric data converted to integer values.
July 8, 2015 at 9:02 am
Hi,
first query result :
1
2
11
20
32
68
100
124
246
256
second query result
A1
A10
A11
AB1
AB10
B1
B2
B21
B31
want to generate the complete output in single select statement.
July 8, 2015 at 9:08 am
Minnu (7/8/2015)
Hi,first query result :
1
2
11
20
32
68
100
124
246
256
second query result
A1
A10
A11
AB1
AB10
B1
B2
B21
B31
want to generate the complete output in single select statement.
Not going to happen since you want to convert the numeric values to integer values.
What are you trying accomplish? Knowing that may help.
July 8, 2015 at 9:08 am
Minnu (7/8/2015)
Hi,first query result :
1
2
11
20
32
68
100
124
246
256
second query result
A1
A10
A11
AB1
AB10
B1
B2
B21
B31
want to generate the complete output in single select statement.
select
ColA = CASE WHEN CastableToInt = 1 THEN cast(Data as int) ELSE NULL END,
ColB = CASE WHEN CastableToInt = 0 THEN Data ELSE NULL END
FROM #TestData
CROSS APPLY (SELECT CastableToInt = CASE WHEN Data not like '%[^0-9]%' THEN 1 ELSE 0 END) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 8, 2015 at 9:09 am
Try using a UNION ALL.
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:13 am
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.
July 8, 2015 at 9:14 am
ChrisM@Work (7/8/2015)
Minnu (7/8/2015)
Hi,first query result :
1
2
11
20
32
68
100
124
246
256
second query result
A1
A10
A11
AB1
AB10
B1
B2
B21
B31
want to generate the complete output in single select statement.
selectColA = CASE WHEN CastableToInt = 1 THEN cast(Data as int) ELSE NULL END,
ColB = CASE WHEN CastableToInt = 0 THEN Data ELSE NULL END
FROM #TestData
CROSS APPLY (SELECT CastableToInt = CASE WHEN Data not like '%[^0-9]%' THEN 1 ELSE 0 END) x
EDIT: Sorry Chris, I missed the cross apply in your query. I didn't scroll down to see it all.
Like this?
select
ColA = CASE WHEN Data not like '%[^0-9]%' THEN cast(Data as int) ELSE NULL END,
ColB = CASE WHEN Data like '%[^0-9]%' THEN Data ELSE NULL END
FROM #TestData;
July 8, 2015 at 9:15 am
Minnu (7/8/2015)
Hi,first query result :
1
2
11
20
32
68
100
124
246
256
second query result
A1
A10
A11
AB1
AB10
B1
B2
B21
B31
want to generate the complete output in single select statement.
How about a simple modification to Lynn's code?
select Data from #TestData where Data not like '%[^0-9]%'
UNION ALL
select Data from #TestData where Data like '%[^0-9]%';
Of course, this isn't any different from just doing a select Data from #TestData.
So, let's step back to the basics here... just what do you want the output to look like?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 8, 2015 at 9:22 am
Numeric values in the first query should be converted to INTEGER.
July 8, 2015 at 9:24 am
Minnu (7/8/2015)
Numeric values in the first query should be converted to INTEGER.
This isn't helpful and doesn't answer the questions which have been posted. Can you please post a table showing exactly what you want your results to look like.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply