تبدیل رشته‌ به عدد در SQL Server بهترین روشها

تبدیل رشته‌ به عدد در SQL Server: راهنمای جامع برای دقت و عملکرد

تبدیل رشته‌ به عدد در SQL Server یک عملیات رایج اما پر از چالش است. داده‌ها اغلب به شکل رشته ذخیره می‌شوند و نیاز به تبدیل دارند، اما قالب‌بندی نامنظم یا وجود کاراکترهای غیرعددی می‌تواند به خطا منجر شود. این راهنما به شما کمک می‌کند تا با روش‌های صحیح و بهینه، رشته‌های عددی را مدیریت کرده و از خطاهای زمان اجرا جلوگیری کنید، با تمرکز بر تکنیک‌های پیشرفته برای مقابله با چالش‌های رایج.

یکی از ابزارهای قدیمی و رایج برای بررسی عددی بودن یک رشته، تابع `ISNUMERIC()` است. این تابع یک خروجی دودویی (0 یا 1) برمی‌گرداند تا نشان دهد آیا یک رشته می‌تواند به نوع عددی تبدیل شود یا خیر.

SELECT ISNUMERIC('123') AS NumTest1,
       ISNUMERIC('123.45') AS NumTest2,
       ISNUMERIC('$123') AS NumTest3,
       ISNUMERIC('123d') AS NumTest4,
       ISNUMERIC('-') AS NumTest5,
       ISNUMERIC('+') AS NumTest6,
       ISNUMERIC('.') AS NumTest7,
       ISNUMERIC(',') AS NumTest8;

این نتایج نشان می‌دهد که `ISNUMERIC` در برخی موارد مانند ‘$123’، ‘-‘، ‘+’ و ‘.’ هم مقدار 1 را برمی‌گرداند، در حالی که این رشته‌ها ممکن است هنگام تبدیل به انواع عددی خاص (مانند `INT`) با خطا مواجه شوند. این رفتار می‌تواند گمراه‌کننده باشد و منجر به خطاهای `Conversion failed when converting …` شود. به همین دلیل، استفاده صرف از `ISNUMERIC` برای اعتبار سنجی کامل، کافی نیست. برای مثال، عبارت زیر با شکست مواجه می‌شود، اگرچه `ISNUMERIC` برای آن 1 برمی‌گرداند:

SELECT CAST('+' AS INT);

برای حل این مشکل و اطمینان از تبدیل امن، SQL Server 2012 توابع `TRY_CONVERT()` و `TRY_CAST()` را معرفی کرد. این توابع به جای ایجاد خطا در صورت عدم موفقیت در تبدیل، مقدار `NULL` را برمی‌گردانند. این قابلیت مدیریت خطا را بسیار ساده‌تر و کد را خواناتر می‌کند، بدون نیاز به بلوک‌های `TRY…CATCH` یا منطق پیچیده با `ISNUMERIC`.

SELECT TRY_CONVERT(INT, '123') AS ConvertedInt1,
       TRY_CONVERT(INT, 'abc') AS ConvertedInt2,
       TRY_CONVERT(DECIMAL(10,2), '123.45') AS ConvertedDecimal1,
       TRY_CONVERT(DECIMAL(10,2), '$123.45') AS ConvertedDecimal2,
       TRY_CAST('+' AS INT) AS CastPlus;

همانطور که مشاهده می‌شود، `TRY_CONVERT` و `TRY_CAST` به سادگی برای رشته‌هایی که قابل تبدیل نیستند، `NULL` برمی‌گردانند، که امکان فیلتر کردن آسان داده‌های نامعتبر یا ارائه مقادیر پیش‌فرض را فراهم می‌کند.

یکی دیگر از چالش‌های رایج، مدیریت جداکننده‌های اعشاری متفاوت (ویرگول یا نقطه) در داده‌های ورودی است. برخی مناطق از ویرگول (`,`) به عنوان جداکننده اعشار استفاده می‌کنند، در حالی که SQL Server معمولاً نقطه (`.`) را به عنوان جداکننده اعشار می‌پذیرد. برای یکسان‌سازی این فرمت‌ها می‌توان از تابع `REPLACE()` استفاده کرد.

فرض کنید رشته‌ای داریم که ممکن است از ویرگول به عنوان جداکننده اعشار استفاده کند، مانند ‘123,45’. برای تبدیل آن به عدد، ابتدا ویرگول را با نقطه جایگزین می‌کنیم:

SELECT TRY_CONVERT(DECIMAL(10,2), REPLACE('123,45', ',', '.')) AS ConvertedFromComma;

اگر داده‌ها پیچیده‌تر باشند و شامل جداکننده‌های هزارگان نیز باشند (مثلاً ‘1.234.567,89’ در قالب اروپایی)، باید ابتدا جداکننده‌های هزارگان را حذف کرده و سپس جداکننده اعشار را استاندارد کنیم.

SELECT TRY_CONVERT(DECIMAL(18,2), REPLACE(REPLACE('1.234.567,89', '.', ''), ',', '.')) AS NormalizedNumber;

تابع `PARSENAME()` که در اصل برای تجزیه نام اشیاء SQL Server (مانند `database.schema.object.column`) طراحی شده است، گاهی اوقات به صورت خلاقانه برای تجزیه رشته‌های عددی پیچیده نیز به کار می‌رود. این روش معمولاً در سناریوهایی استفاده می‌شود که شما نیاز دارید یک رشته را بر اساس یک نقطه به چندین قسمت تقسیم کنید. این تکنیک می‌تواند برای تفکیک بخش‌های صحیح و اعشاری پس از یکسان‌سازی جداکننده‌ها مفید باشد.

برای مثال، اگر شما یک رشته دارید که می‌خواهید قبل و بعد از نقطه اعشار آن را جداگانه بررسی کنید، می‌توانید از `PARSENAME` استفاده کنید. ابتدا باید اطمینان حاصل کنید که فقط یک نقطه در رشته به عنوان جداکننده اعشار وجود دارد.

SELECT
    PARSENAME(REPLACE('123,45', ',', '.'), 2) AS IntegerPart,
    PARSENAME(REPLACE('123,45', ',', '.'), 1) AS DecimalPart;

در این مثال، `PARSENAME` قسمت‌های رشته را پس از جایگزینی ویرگول با نقطه، بر اساس نقطه تجزیه می‌کند. عدد 2 به معنی گرفتن قسمت دوم (قبل از آخرین نقطه) و عدد 1 به معنی گرفتن قسمت اول (بعد از آخرین نقطه) است. این روش برای تفکیک اجزای یک عدد و سپس ترکیب مجدد آن‌ها یا اعتبار سنجی هر بخش به صورت جداگانه کاربرد دارد.

در نهایت، هنگام تبدیل رشته‌ها به اعداد، بهینه‌سازی عملکرد نیز اهمیت دارد. استفاده از `TRY_CONVERT` و `TRY_CAST` عموماً کارآمدتر از رویکرد `ISNUMERIC` به همراه `CASE` و `CAST` است، زیرا نیاز به دو بار ارزیابی رشته (یک بار برای `ISNUMERIC` و یک بار برای `CAST`) را از بین می‌برد. همیشه سعی کنید نوع داده هدف را به دقیق‌ترین شکل ممکن انتخاب کنید تا از هدر رفت حافظه و مشکلات دقت جلوگیری شود. با رعایت این نکات، می‌توانید با اطمینان و کارآمدی بیشتری رشته‌های خود را به مقادیر عددی تبدیل کنید.