Asked  7 Months ago    Answers:  5   Viewed   35 times

I have data like this:

string 1: 003Preliminary Examination Plan   
string 2: Coordination005  
string 3: Balance1000sheet

The output I expect is

string 1: 003
string 2: 005
string 3: 1000

And I want to implement it in SQL.

 Answers

88

First create this UDF

CREATE FUNCTION dbo.udf_GetNumeric
(
  @strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
  DECLARE @intAlpha INT
  SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
  BEGIN
    WHILE @intAlpha > 0
    BEGIN
      SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
      SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
  END
  RETURN ISNULL(@strAlphaNumeric,0)
END
GO

Now use the function as

SELECT dbo.udf_GetNumeric(column_name) 
from table_name

SQL FIDDLE

I hope this solved your problem.

Reference

Tuesday, June 1, 2021
 
antoniputra
answered 7 Months ago
30

Thanks @JonathanAmend

sqlsrv_begin_transaction was opened but wasn't calling sqlsrv_commit.

http://www.php.net/manual/en/function.sqlsrv-commit.php

Saturday, May 29, 2021
 
rblarsen
answered 7 Months ago
80

The answer is given in the comment by Ilja above:

The "namespace" is a so called schema and has to be declarated in the mapped object. Given the example from the opening post, the mapped table has to be defined like this:

class IndexedElements(Base):
    __tablename__ = "IndexedElements"
    __table_args__ = {"schema": "Reporting"}

    UniqueID = Column(String,primary_key=True)
    ValueDate = Column(DateTime)
    Value = Column(Float)

Or define a base class containing these informations for different schemata. Check also "Augmenting the base" in sqlalchemy docs: http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#augmenting-the-base

Friday, August 27, 2021
 
ahmelsayed
answered 3 Months ago
16

This solution will take two first numbers, each can have any number of digits

string s =  "AS_!SD 2453iur ks@d9304-52kasd";

MatchCollection matches = Regex.Matches(s, @"d+");

string[] result = matches.Cast<Match>()
                         .Take(2)
                         .Select(match => match.Value)
                         .ToArray();

Console.WriteLine( string.Join(Environment.NewLine, result) );

will print

2453
9304

you can parse them to int[] by result.Select(int.Parse).ToArray();

Tuesday, August 31, 2021
 
Pascal
answered 3 Months ago
62

if awk is not a must:

grep -o '[0-9]+'

example:

kent$ echo "ref12345678"|grep -o '[0-9]+'
12345678

with awk for your example:

kent$ echo "ref12345678"|awk -F'[^0-9]*' '$0=$2'     
12345678
Tuesday, September 7, 2021
 
Shawn
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share