Sql - How do I get only the numbers after the decimal?

Approaching decimal extraction: several tools and methods

PARSENAME: String it along

PARSENAME disassembles the number when you're wrangling decimal numbers formatted as strings:

SELECT PARSENAME(CONVERT(varchar, YourColumn), 1) AS DecimalDigits FROM YourTable;


Comment: // Got String Art? Take it apart!

Can you handle it?: special case of negative numbers

When dealing with uncooperative negative numbers, ABS can help you maintain peace and positivity:

SELECT ABS(YourColumn) - FLOOR(ABS(YourColumn)) AS DecimalPart FROM YourTable;


Comment: // Tell negativity: "Not on my watch!"

Exacting extraction: precision with SUBSTRING

Turn your decimals into a varchar for SUBSTRING precision operation that even Swiss watchmakers would envy:

SELECT SUBSTRING(CONVERT(varchar, YourColumn), CHARINDEX('.', YourColumn) + 1, LEN(YourColumn)) AS DecimalDigits FROM YourTable;


Comment: // Precision, my dear Watson, is the key!

Considerations, edge cases, and the kitchen sink

Scale and precision: not just for bathroom scales

Solutions need to respect the scale and precision of your decimal data. Because numbers deserve respect, too:

SELECT CONVERT(DECIMAL(10,2), YourColumn % 1)
FROM YourTable;


Comment: // Give your numbers the respect (scale and precision) they deserve!

Fixing zero hero: leading and trailing zeros

Manage zeros with tact. Strip trailing zeros with flair:

SELECT TRIM(TRAILING '0' FROM CONVERT(VARCHAR, YourColumn % 1))
FROM YourTable;


Comment: // Say, who invited all these zeros to the party?

Test drives and challenge rounds

Hold extensive dress rehearsals of your solution with widely variant decimals. Even those stuck-up negative ones.

Get detailed with CHARINDEX and LEN

In SQL, CHARINDEX and LEN columns are your friends for complex extraction jobs. Invite them for sleepovers often.