Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
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:

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
Updated 17-Jun-20 17:32pm
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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

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
 */
   
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100