locked
How to fetch a string from a table column in sql RRS feed

  • Question

  • User2001432087 posted

    Hi,

       I am having a varchar datatype column in a table . For Example in the column if the string is "ABC001" I want to retrieve "ABC" from the string and store in variable. The string may be differ in length it was not in fixed length. Please suggest me how to resolve.

    Tuesday, August 16, 2016 7:08 AM

Answers

  • User-595703101 posted

    Hello ykkumar13,

    Please read the SQL tutorial Remove Non-Numeric Character in SQL String Expression where a user function is created to remove alpha-numeric values in a string expression or character type field value

    We can change the script given there and use it to clear numeric values from string as follows:

    create function ClearNumericCharacters(@str nvarchar(max))
    returns nvarchar(max)
    as
    begin
     while patindex('%[0-9]%', @str) > 0
      set @str = stuff(@str, patindex('%[0-9]%', @str), 1, '')
     return @str
    end 
    

    In referred tutorial, there are samples how you can use the sql function on a variable or with columns of a database table,

    I hope it helps,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 16, 2016 10:47 AM
  • User77042963 posted
    CREATE TABLE Contacts  ( mixedCol NVARCHAR(100) )
     
        INSERT INTO Contacts  VALUES ( 'abc001'),( 'efg56789')
     
    --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
     
    ,processCTE as (
        select  mixedCol, col  as Number_removed_mixedCol   
        from Contacts Cross Apply (
                select (select i + ''
                from (select N, substring(mixedCol, N, 1) i from Nums  
                where N<=datalength(mixedCol)) t
                where PATINDEX('%[^0-9]%',i)> 0
                order by N
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ) p0 (col)  
     
    ) 
    SELECT  mixedCol,   Number_removed_mixedCol   FROM processCTE
     
    drop table Contacts

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 16, 2016 1:52 PM

All replies

  • User-821857111 posted

    You can use Regex for this. 

    var input = "ABC001";
    var match = Regex.Match(input, @"[^\d]+");
    var result = match.Groups[0].Value;

    Tuesday, August 16, 2016 7:18 AM
  • User2001432087 posted

    Actually I want to fetch the string in SQL procedure itself. Can you suggest me how to resolve.

    Tuesday, August 16, 2016 7:29 AM
  • User-595703101 posted

    Hello ykkumar13,

    Please read the SQL tutorial Remove Non-Numeric Character in SQL String Expression where a user function is created to remove alpha-numeric values in a string expression or character type field value

    We can change the script given there and use it to clear numeric values from string as follows:

    create function ClearNumericCharacters(@str nvarchar(max))
    returns nvarchar(max)
    as
    begin
     while patindex('%[0-9]%', @str) > 0
      set @str = stuff(@str, patindex('%[0-9]%', @str), 1, '')
     return @str
    end 
    

    In referred tutorial, there are samples how you can use the sql function on a variable or with columns of a database table,

    I hope it helps,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 16, 2016 10:47 AM
  • User77042963 posted
    CREATE TABLE Contacts  ( mixedCol NVARCHAR(100) )
     
        INSERT INTO Contacts  VALUES ( 'abc001'),( 'efg56789')
     
    --===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
     
    ,processCTE as (
        select  mixedCol, col  as Number_removed_mixedCol   
        from Contacts Cross Apply (
                select (select i + ''
                from (select N, substring(mixedCol, N, 1) i from Nums  
                where N<=datalength(mixedCol)) t
                where PATINDEX('%[^0-9]%',i)> 0
                order by N
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ) p0 (col)  
     
    ) 
    SELECT  mixedCol,   Number_removed_mixedCol   FROM processCTE
     
    drop table Contacts

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 16, 2016 1:52 PM
  • User2001432087 posted

    Thank you very much it is working for me.

    Thursday, August 18, 2016 5:07 AM