How to make User Defined Function split column to two columns text and numbers ?[Answered] RSS

• • Reply
  ahmedbarbary

  Member

  39 Points

  359 Posts

  How to make User Defined Function split column to two columns text and numbers ?

  Jun 17, 2020 11:21 PM|ahmedbarbary|LINK

  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

• • Reply
  Yang Shen

  Contributor

  3140 Points

  983 Posts

  Re: How to make User Defined Function split column to two columns text and numbers ?

  Jun 18, 2020 01:46 AM|Yang Shen|LINK

  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