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

Question

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
June 17, 2020 at 11:24 pm
Go to Answer
#3764057
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 3 years, 10 months ago by ahmed_elbarbary.2010. Reason: for more clear data

Jeff Moden

SSC Guru

Points: 1003895
More actions
June 18, 2020 at 3:19 am
Answer
#3764074
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

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

pietlinden SSC Guru Points: 63436 More actions · Answer 1

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;

pietlinden SSC Guru Points: 63436 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 3

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.

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

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 4

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
withE1(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 3 years, 10 months ago by Chris Wooding.