Hi ahmedbarbary,

ahmedbarbary

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 ..

The requirement is easy to achieve, but i don't think it's a good idea to create a UDF for that since the query here is for the entire table, while create a UDF for a table would complicate your simple implementation.

In fact, the query contains only one simple statement using PATINDEX:

select LEFT(columnTextNumbers,PATINDEX('%[0-9][^0-9.]%',columnTextNumbers)) as columnNumberValues, LTRIM(RIGHT(columnTextNumbers, LEN(columnTextNumbers) - PATINDEX('%[0-9][^0-9.]%', columnTextNumbers ))) As columnTextValues from #temp 

Or if you want to select the query result to another table, use SQL SELECT INTO Statement:

select LEFT(columnTextNumbers,PATINDEX('%[0-9][^0-9.]%',columnTextNumbers)) as columnNumberValues, LTRIM(RIGHT(columnTextNumbers, LEN(columnTextNumbers) - PATINDEX('%[0-9][^0-9.]%', columnTextNumbers ))) As columnTextValues into #AAA from #temp 
select * from #AAA

Best Regard,

Yang Shen