How to fetch a string from a table column in sql [Answered]RSS

• • Reply
  ykkumar13

  Member

  1 Points

  109 Posts

  How to fetch a string from a table column in sql

  Aug 16, 2016 07:08 AM|ykkumar13|LINK

  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.

• • Reply
  Mikesdotnett...

  All-Star

  184748 Points

  26712 Posts

  Moderator

  MVP

  Re: How to fetch a string from a table column in sql

  Aug 16, 2016 07:18 AM|Mikesdotnetting|LINK

  You can use Regex for this. 

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

  Cheers, Mike
  
  ASP.NET Tutorials | Learn Entity Framework Core| Learn Razor Pages
• • Reply
  ykkumar13

  Member

  1 Points

  109 Posts

  Re: How to fetch a string from a table column in sql

  Aug 16, 2016 07:29 AM|ykkumar13|LINK

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

• • Reply
  eralper

  Contributor

  5981 Points

  1413 Posts

  Re: How to fetch a string from a table column in sql

  Aug 16, 2016 10:47 AM|eralper|LINK

  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,

  T-SQL programming SQL Server Tutorials
  SQL Server 2016
  
• • Reply
  limno

  All-Star

  122238 Points

  9702 Posts

  Moderator

  Re: How to fetch a string from a table column in sql

  Aug 16, 2016 01:52 PM|limno|LINK

  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

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  ykkumar13

  Member

  1 Points

  109 Posts

  Re: How to fetch a string from a table column in sql

  Aug 18, 2016 05:07 AM|ykkumar13|LINK

  Thank you very much it is working for me.