How to do User Defined Function take input as text and output text and numbers ?

  • problem

    How to make UserDefinedFunction on sql server 2012 split column text to two columns .

    first column include text characters as unit measure only and

    second column include numbers only in case of integer or decimal etc..?

    create table #temp
    (
    columnTextNumbers nvarchar(50)
    )
    insert into #temp (columnTextNumbers)
    values
    ('3.5A'),
    ('5.50kg'),
    ('35.70kg'),
    ('9m')

    I need to make function split column name columnTextNumbers exist on temp table #temp to

    two columns

    first column is will be columnTextValues include only units measure .

    second column will be columnNumberValues include only numbers if integer or decimal etc ..

    so Input will be one value have text and numbers

    and output result will be two columns as below :

    columnNumberValues     columnTextValues
    3.5 A
    5.50 kg
    35.70 kg
    9 m

    • This topic was modified 5 months, 1 week ago by  ahmed_elbarbary.2010. Reason: for more clear data
  • This is ugly, but appears to work. (Someone who knows how to do this better than I will undoubtedly chime in and show a much better way of doing it, but in the meantime...)

    create function GetRightmostNumberPosition (
    @InputString VARCHAR(10)
    )
    RETURNS INT
    AS
    BEGIN
    DECLARE @i INT;
    DECLARE @ltr CHAR;

    SET @i = LEN(@InputString)
    -- start at end and work backwards toward front
    SET @ltr = SUBSTRING(@InputString,@i,1)

    WHILE ISNUMERIC(@ltr) = 0
    -- work right to left to find the first non-numeric character
    BEGIN
    SET @i = @i - 1;
    SET @ltr = SUBSTRING(@InputString,@i,1);
    END
    return @i;
    END

    Create some test data...

    CREATE TABLE #TestValues (SomeValue VARCHAR(7) NOT NULL);
    GO
    INSERT INTO #TestValues(SomeValue) VALUES ('3.5A'), ('5.50kg'), ('35.70kg'), ('9m');

    "Solution"

    SELECT tv.SomeValue
    , LeftSide = LEFT(tv.SomeValue, dbo.GetRightmostNumberPosition(tv.SomeValue))
    , RightSide = RIGHT(tv.SomeValue, LEN(tv.SomeValue) - LEN(LEFT(tv.SomeValue, dbo.GetRightmostNumberPosition(tv.SomeValue))))
    FROM #TestValues tv;
  • If the data is that consistent (some decimal value followed by character values with no spaces), no loops required, UDF scalars, or other forms of RBAR required. Just go for where the type of character changes as the split position using PATINDEX to find it.

    I used CROSS APPLY to find the position just to DRY the code out.

     SELECT  Value = SUBSTRING(columnTextNumbers,1,ca.Posit)
    ,UoM = SUBSTRING(columnTextNumbers,Posit+1,50)
    FROM #temp
    CROSS APPLY (SELECT PATINDEX('%[0-9.][^.0-9]%',columnTextNumbers)) ca (Posit)
    ;

    Results:

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Is there helpful documentation on the PATINDEX function somewhere? the stuff on the MSFT site is useless.

  • The MS documentation is meant to be a reference rather than a "how to use" tutorial.  The key to the problem on this post is to "find the first non-numeric character in a string".  If you search for that on Yabingooducklehoo, lot's of mostly decent examples will show up.

    Speaking of that, the MS documentation does actually have an example of what this problem needed to be solved.  Example "C" explains that the pattern is actually the same as that used in LIKE and example "D" provides code very similar to what is needed to solve this problem (I just took it one step further).

    The documentation for PATINDEX that you're looking for is actually for the pattern and you have to look at the documentation under LIKE for that.

    Shifting gears a bit, I did look for more comprehensive tutelage on PATINDEX using a search for "how to use patindex in sql server with example" and it was kind of frighting because it doesn't actually teach you how to "think" about how you might use PATINDEX to solve a problem that you can't easily find a solution to on the internet.

    So to answer your question, no... I don't know of a good link that will teach folks how to "think" about how to use PATINDEX to solve such problems.  If you look into my code, there's a whole lot more "technique" involved than just using PATINDEX... I used CROSS APPLY to DRY (Don't Repeat Yourself) out the code so the PATINDEX formula wouldn't have to be used in the code more than once, which may also help performance.  If you also look at the documentation for CROSS APPLY, it "sucks" the same way because it isn't a complete tutorial on everywhere you could use it and doesn't even mention using it to DRY out code.

    As a bit of a sidebar, that's why I answer questions on forums because no article in the world will contain all the possible uses even for something as PATINDEX.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If the text part could appear before the number part, you could use Alan Burstein's pattern exclude function thus.

    create table #temp
    (
    columnTextNumbers nvarchar(50)
    )
    insert into #temp (columnTextNumbers)
    values
    ('3.5A'),
    ('5.50kg'),
    ('35.70kg'),
    ('9m'),
    ('£4.99')

    select t.columnTextNumbers, upe1.NewString as NumberValues, upe2.NewString as AlphaValues
    from #temp t
    cross apply ufn_PatternExclude(t.columnTextNumbers, '[^0-9.]') upe1
    cross apply ufn_PatternExclude(t.columnTextNumbers, '[0-9.]') upe2

    PatternExclude Results

    The function is defined below.

    create function [ufn_PatternExclude]
    (
    @String varchar(8000),
    @Pattern varchar(50)
    )
    /*******************************************************************************
    Purpose:
    Given a string (@String) and a pattern (@Pattern) of characters to remove,
    remove the patterned characters from the string.

    Usage:
    --===== Basic Syntax Example
    SELECT NewString
    FROM dbo.ufn_PatternExclude(@String,@Pattern);

    --===== Remove all but Alpha characters
    SELECT NewString
    FROM dbo.SomeTable st
    CROSS APPLY dbo.ufn_PatternExclude(st.SomeString,'[^A-Za-z]');

    --===== Remove all but Numeric digits
    SELECT NewString
    FROM dbo.SomeTable st
    CROSS APPLY dbo.ufn_PatternExclude(st.SomeString,'[^0-9]');

    Programmer Notes:
    1. @Pattern is not case sensitive (the function can be easily modified to make it so)
    2. There is no need to include the "%" before and/or after your pattern since since we
    are evaluating each character individually

    Revision History:
    Rev 00 - 10/27/2014 Initial Development - Alan Burstein

    Rev 01 - 10/29/2014 Mar 2007 - Alan Burstein
    - Redesigned based on the dbo.STRIP_NUM_EE by Eirikur Eiriksson
    (see: http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx)
    - change how the cte tally table is created
    - put the include/exclude logic in a CASE statement instead of a WHERE clause
    - Added Latin1_General_BIN Colation
    - Add code to use the pattern as a parameter.

    Rev 02 - 11/6/2014
    - Added final performance enhancement (more kudos to Eirikur Eiriksson)
    - Put 0 = PATINDEX filter logic into the WHERE clause

    Rev 03 - 5/16/2015
    - Updated code to deal with special XML characters

    Rev 04 - 25 Oct 2017 - Chris Wooding
    - Removed redundant conversion of LEN to int.
    - Set @String to '' instead of null before checking length for TOP clause.

    *******************************************************************************/
    returns table
    with schemabinding
    as
    return
    with E1(N)
    as (select N
    from (values (null), (null), (null), (null), (null),
    (null), (null), (null), (null), (null) ) as X (N)),
    itally(N)
    as (select top (len(isnull(@String, ''))) row_number() over (order by (select null))
    from E1 T1
    cross join E1 T2
    cross join E1 T3
    cross join E1 T4)
    select NewString = ((
    select substring(@String, N, 1)
    from itally
    where 0 = patindex(@Pattern, substring(@String collate Latin1_General_BIN, N, 1))
    for xml path(''), type).value('.[1]', 'varchar(8000)'));

    go

    • This reply was modified 4 months, 4 weeks ago by  Chris Wooding.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply