How to make user defined function split column to two columns text and numbers ?

Question

1.00/5 (1 vote)

See more:

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

What I have tried:

SQL

SELECT subsrtunit,LEFT(subsrtnumeric, PATINDEX('%[^0-9.]%', subsrtnumeric+'t') - 1)
FROM (
    SELECT subsrtunit = SUBSTRING(string, posofchar, LEN(string)),
  subsrtnumeric = SUBSTRING(string, posofnumber, LEN(string))
    FROM (
        SELECT string, posofchar = PATINDEX('%[^0-9.]%', string),
      posofnumber = PATINDEX('%[0-9.]%', string)
        FROM #temp
    ) d
) t

Posted 17-Jun-20 14:02pm

ahmed_sa

Updated 17-Jun-20 17:32pm

Add a Solution

Comments

MadMyche 17-Jun-20 20:40pm

AND WHAT HAPPENS?
About the yelling..... after well over 250 questions, you haven't figured out we are going to ask this

ahmed_sa 17-Jun-20 21:56pm

I need to make result above as SQL function so how to do that .
what I try give me correct result only what i need to do converting code above to SQL function

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ahmed_sa · Accepted Answer · 2020-06-17T17:33:00

thank you for help and support it solved
and this is my solution :

SQL

create FUNCTION myfuncolumnTextNumbers (@str varchar(500)) 
RETURNS TABLE
AS RETURN
(
 
--===== 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)
 
,processTable as (
    select   col1  as textCol,col2 as NumCol   
    from (select @str  as columnTextNumbers) #temp Cross Apply (
            select (select C + ''
            from (select N, substring(columnTextNumbers, N, 1) C from Nums  
            where N<=datalength(columnTextNumbers)) t
            where PATINDEX('%[^0-9.]%',C)> 0
            order by N
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ) p0 (col1) 
	  Cross Apply (
            select (select C + ''
            from (select N, substring(columnTextNumbers, N, 1) C from Nums  
            where N<=datalength(columnTextNumbers)) t
            where PATINDEX('%[0-9.]%',C)> 0
            order by N
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ) p1 (col2)   
 ) 


SELECT     textCol, NumCol    FROM processTable

 )

 
 


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

 
SELECT  columnTextNumbers,   textCol, NumCol    
 FROM  #temp
 cross apply dbo.myfuncolumnTextNumbers(columnTextNumbers) d



drop table #temp
 /*
 columnTextNumbers	textCol	NumCol
3.5A	A	3.5
5.50kg	kg	5.50
35.70kg	kg	35.70
9m	m	9
 */