function that does spefic conversion

Question

function that does spefic conversion

kcj

Mr or Mrs. 500

Points: 540
More actions
February 20, 2015 at 10:03 am

#304402

I would like to create a function that take a value and run some logic and output the value
I have a table like this
Table A
value
*
001
004.00
3.0
1.22
Logic I want to run is
The value that you are passing is numeric and numeric with only decimal 0 value, and then convert it to integer otherwise leave as it is
So if I run a query something like this
Select value, fn_convertointerger(value) as converted_value from TableA
I will get
Value converted_value
* *
001 1
004.00 4
3.0 3
1.22 1.22
2.02 2.02
4.000 4
Jkil& Jkil&
How can I create a function like this to convert specific numeric value?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

You can't.

You can make a function/query with output that LOOKS like that, but your output will be a string even it it looks like an integer/decimal for some rows. The type can't change dynamically row to row.

And basically your solution will be a big case statement that examines the input, converts it to a number if numeric, converts to an integer if whole, and converts everything back to a string before the output.

kcj Mr or Mrs. 500 Points: 540 More actions · Answer 2

thanks..

what if i want to convert any numeric value to integer ,is it possble to do it in function?

if so, can you show me some examples?

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Have a look at TRY_CONVERT https://msdn.microsoft.com/en-us/library/hh230993.aspx

You'll need to do some extra CASTing and probably an ISNULL aswell to achieve what you want.

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok technically it's possible, but not practical to consume later.

you can use a sql_variant, which allows individual values to hold their datatype...one field can be an int, and another a decimal, and another a date, all in the same column.

an example that dwain.c made here:

http://www.sqlservercentral.com/Forums/Topic1656749-392-1.aspx#bm1656760

dwain.c (2/2/2015)
Lowell (2/2/2015)
...maybe switch to using a sql_variant.
What a brilliant idea!
WITH SampleData (ID, Pay) AS
(
SELECT 1, CAST(1.000 AS NUMERIC(19,3))
UNION ALL SELECT 2,2.250
UNION ALL SELECT 3,3.445
UNION ALL SELECT 4,6.000
)
SELECT ID, Pay
,PayFormatted=CASE WHEN FLOOR(Pay) = Pay THEN CAST(CAST(Pay AS INT) AS SQL_VARIANT) ELSE Pay END
FROM SampleData;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kcj Mr or Mrs. 500 Points: 540 More actions · Answer 5

actually what i am trying to do is

logic something like below that I am going to put in a function .. but value field in table A is nvarchar type. and i am getting error like :Conversion failed when converting the nvarchar value '054.00' to data type int. How can I fix this issue? am i taking the right approach? also ISNUMERIC function doesnt seems working all the time

Select

Distinct value as value ,CASE WHEN ISNUMERIC(value) <> 0 THEN CONVERT(int,value) ELSE value END AS converted_value From TableA

Nevyn SSChampion Points: 14210 More actions · Answer 6

Here is his Lowell's sample modified for what you want.

WITH SampleData (ID, Pay) AS

(

SELECT 1, '1.000'

UNION ALL SELECT 2,'2.250'

UNION ALL SELECT 3,'3.445'

UNION ALL SELECT 4,'6.000'

UNION ALL SELECT 5,'this aint a number'

)

SELECT ID, Pay

,PayFormatted=CAST (CASE

WHEN ISNUMERIC(Pay) = 1 THEN CASE

WHEN FLOOR(Pay) = Pay THEN CAST(CAST(CAST (Pay AS NUMERIC(19,3)) AS INT) AS SQL_VARIANT)

ELSE CAST(CAST (Pay AS NUMERIC(19,3)) AS sql_variant) END

ELSE Pay

END

AS SQL_VARIANT)

FROM SampleData;

I was unaware of sql_variant. Learn something new every day.

EDIT: I actually took it further and gave you numerics too. Re-reading your requirements you'd want

WITH SampleData (ID, Pay) AS

(

SELECT 1, '1.000'

UNION ALL SELECT 2,'2.250'

UNION ALL SELECT 3,'3.445'

UNION ALL SELECT 4,'0006.000'

UNION ALL SELECT 5,'this aint a number'

)

SELECT ID, Pay

,PayFormatted=CAST (CASE

WHEN ISNUMERIC(Pay) = 1 AND FLOOR(Pay) = Pay THEN CAST(CAST(CAST (Pay AS NUMERIC(19,3)) AS INT) AS SQL_VARIANT)

ELSE Pay

END

AS SQL_VARIANT)

FROM SampleData;

That being said, I am still deeply uncomfortable with mixing types between rows.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

jung-387933 (2/20/2015)
actually what i am trying to do is
logic something like below that I am going to put in a function .. but value field in table A is nvarchar type. and i am getting error like :Conversion failed when converting the nvarchar value '054.00' to data type int. How can I fix this issue? am i taking the right approach? also ISNUMERIC function doesnt seems working all the time
Select
Distinct value as value ,CASE WHEN ISNUMERIC(value) <> 0 THEN CONVERT(int,value) ELSE value END AS converted_value From TableA

Your logic is backwards. When ISNUMERIC = 0 then it can possibly be converted to an int.

You mention that ISNUMERIC doesn't seem to be working. Actually it is working exactly as it should, unfortunately that isn't at all what it seems like it should. Check out this article about ISNUMERIC. http://www.sqlservercentral.com/articles/ISNUMERIC%28%29/71512/[/url]

BTW, using a scalar function is going to be horrible for performance and is still going to output a string that looks a lot like a number. You can't mix datatypes in a given column. Well as Lowell stated above you could use sql_variant but that datatype is not recommended except in very rare situations.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

It sounds to me like you've got a much bigger problem on your hands than just being able to find numeric values. If you're storing numeric values in a nvarchar column, that's the problem that needs to be addressed. Is the data that's supposed to stored in that column always supposed to be numeric? If so, then you need to find a way to clean out anything that's invalid and get the data into a column with the right data type. The design of the table will defend itself against invalid data getting in there in the first place.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Nevyn SSChampion Points: 14210 More actions · Answer 9

Select
Distinct value as value ,CASE WHEN ISNUMERIC(value) <> 0 THEN CONVERT(int,value) ELSE value END AS converted_value From TableA

This fails for two reasons.

First, '1.0' for example passes the ISNUMERIC test, but can't be cast as an int/

Second, you have part of your case trying to return an INT while the rest returns a string. And that won't work predictably. So this is where the cast as sql_variant is helping.

As to why its a bad idea, what is going to be calling your function/query, and what is it expecting to get? You could have unexpected results.