sql server - SQL take just the numeric values from a varchar

Sql server - SQL take just the numeric values from a varchar

If you want to extract only numeric values from a varchar column in SQL Server, you can use a combination of string manipulation functions. Here's an example:

Assuming you have a table named YourTable with a varchar column named YourColumn:

SELECT YourColumn, ISNULL(CAST('' AS INT), 0) + CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(YourColumn, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') AS INT) AS NumericValue FROM YourTable;

Explanation:

  • The REPLACE function is used to remove all non-numeric characters from the string.
  • The CAST function is used to convert the cleaned string to an integer. If the string is not a valid integer, it will be treated as 0.
  • The ISNULL function is used to handle cases where the string is empty or does not contain any numeric characters. It returns 0 in such cases.

Replace YourTable and YourColumn with your actual table and column names.

Keep in mind that this approach assumes that you want to treat consecutive numeric characters as a single numeric value. If you have specific rules for extracting numeric values (e.g., handling decimal points, negative numbers), you may need a more sophisticated approach or use a user-defined function.

Examples

  1. Extract Numeric Values using PATINDEX and SUBSTRING:

    DECLARE @String VARCHAR(MAX) = 'Your string with 123 numeric values'; SELECT SUBSTRING(@String, PATINDEX('%[0-9]%', @String), LEN(@String) - PATINDEX('%[0-9]%', @String) + 1) AS NumericValues;

    Description: Uses PATINDEX to find the first numeric character and extracts numeric values using SUBSTRING.

  2. SQL Server Extract Numbers using REPLACE and ISNUMERIC:

    DECLARE @String VARCHAR(MAX) = 'Your string with 123 numeric values'; SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String, ' ', ''), '-', ''), '+', ''), '.', ''), ',', '') AS NumericValues WHERE ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@String, ' ', ''), '-', ''), '+', ''), '.', ''), ',', '')) = 1;

    Description: Removes non-numeric characters using REPLACE and checks numeric validity using ISNUMERIC.

  3. Extract Numbers from String using CLR Function:

    CREATE FUNCTION dbo.GetNumericValues(@String VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS EXTERNAL NAME YourAssemblyName.YourClassName.GetNumericValues;

    Description: Utilizes a CLR (Common Language Runtime) function to extract numeric values.

  4. SQL Server Extract Digits using Recursive CTE:

    DECLARE @String VARCHAR(MAX) = 'Your string with 123 numeric values'; WITH DigitsCTE AS ( SELECT SUBSTRING(@String, 1, 1) AS Digit, SUBSTRING(@String, 2, LEN(@String)) AS Remaining WHERE ISNUMERIC(SUBSTRING(@String, 1, 1)) = 1 UNION ALL SELECT SUBSTRING(Remaining, 1, 1) AS Digit, SUBSTRING(Remaining, 2, LEN(Remaining)) FROM DigitsCTE WHERE ISNUMERIC(SUBSTRING(Remaining, 1, 1)) = 1 ) SELECT STRING_AGG(Digit, '') WITHIN GROUP (ORDER BY (SELECT NULL)) AS NumericValues FROM DigitsCTE;

    Description: Uses a recursive CTE to extract numeric digits and aggregates them into a string.

  5. Extract Numbers from String using XML:

    DECLARE @String VARCHAR(MAX) = 'Your string with 123 numeric values'; SELECT CAST('<x>' + REPLACE(@String, ' ', '</x><x>') + '</x>' AS XML).value('(/x[. cast as xs:integer?])', 'VARCHAR(MAX)') AS NumericValues;

    Description: Utilizes XML to split the string into values and casts them to integers.

  6. SQL Server Extract Numeric Values using CLR Scalar Function:

    CREATE FUNCTION dbo.GetNumericValues(@String VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS EXTERNAL NAME YourAssemblyName.YourClassName.GetNumericValues;

    Description: Implements a CLR scalar function to extract numeric values.

  7. Extract Numbers from String using WHILE Loop:

    DECLARE @String VARCHAR(MAX) = 'Your string with 123 numeric values'; DECLARE @Index INT = 1, @Result VARCHAR(MAX) = ''; WHILE @Index <= LEN(@String) BEGIN IF ISNUMERIC(SUBSTRING(@String, @Index, 1)) = 1 SET @Result += SUBSTRING(@String, @Index, 1); SET @Index += 1; END SELECT @Result AS NumericValues;

    Description: Uses a WHILE loop to iterate through the string and extract numeric values.

  8. SQL Server Extract Numbers using CROSS APPLY and VALUES:

    DECLARE @String VARCHAR(MAX) = 'Your string with 123 numeric values'; SELECT STRING_AGG(Value, '') AS NumericValues FROM (SELECT Value FROM STRING_SPLIT(@String, '') WHERE ISNUMERIC(Value) = 1) AS Numbers;

    Description: Utilizes CROSS APPLY with STRING_SPLIT to split the string into values and filters numeric ones.

  9. Extract Numbers from String with Regular Expression:

    DECLARE @String VARCHAR(MAX) = 'Your string with 123 numeric values'; SELECT CAST(N'<' + REPLACE(@String, ' ', '') + N'>' AS XML).value('(/root/text())[1]', 'VARCHAR(MAX)') AS NumericValues;

    Description: Uses a regular expression-like approach with XML to extract numeric values.

  10. SQL Server Extract Numbers using JSON:

    DECLARE @String VARCHAR(MAX) = 'Your string with 123 numeric values'; SELECT STRING_AGG(Value, '') AS NumericValues FROM OPENJSON('["' + REPLACE(@String, ' ', '","') + '"]') WITH (Value NVARCHAR(MAX) '$' AS JSON);

    Description: Converts the string to a JSON array and uses OPENJSON to extract numeric values.

More Tags

long-integer html-injections page-break-inside words nsnotificationcenter react-responsive-carousel having temporary-files infix-notation pkix

More Programming Questions

More Entertainment Anecdotes Calculators

More Biochemistry Calculators

More Tax and Salary Calculators

More Animal pregnancy Calculators