如何递增nvarchar类型字段中类似‘0/0’格式字符串的第一个数字？

Got it, let's fix this problem properly! Your current approach only grabs the first character, which won't work if the first number has multiple digits (like 10/3). Here's a robust way to increment the first number and return the full formatted string:

Step-by-Step Breakdown #

We need to handle three key steps to get the desired result:

1. Locate the position of the / to split the string into two parts.
2. Extract the first number, convert it to an integer, and increment it by 1.
3. Convert the incremented number back to a string, then concatenate it with the original trailing part (starting from the /).

Working SQL Query #

For a single test string (like your '0/0' example):

SELECT 
    CAST(CAST(SUBSTRING('0/0', 1, CHARINDEX('/', '0/0') - 1) AS INT) + 1 AS NVARCHAR(50))
    + SUBSTRING('0/0', CHARINDEX('/', '0/0'), LEN('0/0')) AS incremented_string;

This will output 1/0 exactly as you need.

If you're working with a table column (let's say your column is named target_col in your_table):

SELECT 
    CAST(CAST(SUBSTRING(target_col, 1, CHARINDEX('/', target_col) - 1) AS INT) + 1 AS NVARCHAR(50))
    + SUBSTRING(target_col, CHARINDEX('/', target_col), LEN(target_col)) AS incremented_string
FROM your_table;

Why This Works #

• CHARINDEX('/', target_col): Finds the exact position of the first / in the string, so we know where to split.
• SUBSTRING(..., 1, CHARINDEX(...) - 1): Extracts the entire first number (even if it's multiple digits like 123/45), not just the first character.
• Casting to INT lets us safely increment the number, then we cast back to NVARCHAR to match the original string type for concatenation.
• The second SUBSTRING grabs everything from the / onwards, preserving the original second part of the string.

Example Test Cases #

• Input 5/7 → Output 6/7
• Input 12/34 → Output 13/34
• Input 0/99 → Output 1/99

内容的提问来源于stack exchange，提问作者TheOne__