It only split data by decimal point.

SELECT PARSENAME('abcv.qwe',1)

qwe

SELECT PARSENAME('abcv.qwe',2)

abcv

SELECT PARSENAME('$12,345',2) --NULL

Assuming all values have decimal points. A potentially hazardous assumption...

Interesting use of PARSENAME although its application is not well suited to the removal of decimal 'precision'.

First, as already pointed out by Erik, it relies on the PARSEd object containing a '.' so you'd need to perform something similar to COALESCE(PARSENAME('$12,345', 2), PARSENAME('$12,345', 1)) to handle cases where it does not.

Second, there are globalisation issues. In Spain, for example, number formats take the form 12.345,00 ie '.' is a thousands separator while ',' is a decimal separator so in a Spainish locale, the solution would return '$12' !

That said, without knowing more about how PARSENAME is implemented under the covers, I'll keep this trick in mind. It could work well to parse IP numbers or other strictly formed numeric strings which are not affected by the issues cited above.

It seems to handle up to four points. Anything more than four points in a string and all the output becomes NULL. I'm not aware of the original intended use for this function however, so this probably doesn't come as a surprise. Likely this was just to demonstrate the mechanics of the function.

Thanks a lot for your inputs/feedback !!!

Yes, I Agree this is not full proof solution. But, assuming given conditions(For US Currency and applying usablility for decimal datatype column) this is the best way to do, I found.

Thanks Again !!!

• Chirag

Yes, I Agree this is not full proof solution. But, assuming given conditions(For US Currency and applying usablility for decimal datatype column) this is the best way to do, I found.

Without knowing your requirements it's hard to validate the best way. If you're simply storing the result of PARSENAME this might be adequate. More likely though you're converting the result to something more useful like an integer. If so, CAST(FLOOR(CAST(amountString AS money)) AS int) is better.

Yes , I just have to print the result nothing more than that. And hence I used same.

• Chirag

If you need to take a number with values to the right of the decimal point and remove them, leaving only the whole integer portion, you have better options.

Cast to int or bigint Use the ROUND, FLOOR, OR CEILING functions

Doing mathematical operations using string parsing is always going to be problematic.

Thanks Marc,

Requirement here was to kept formatting ($ ##,##,###) as it is. I don't think any mathematical functions will kept it as it.

Thanks, Chirag

If you cast your result to MONEY, the presentation format will be determined by the internationalization settings on the server. You should separate the data from the format, otherwise calculations become very difficult to support. If you are dealing with currency data, the appropriate data type is [money], not a text type ([varchar], [nvarchar], [char], [nchar]