How to make User Defined Function split column to two column

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

Hi,

You can use below function to get desire values, If you wish you can create single function with one more parameter which will take eithere numericvalues or alpha value

-- For Numeric values
CREATE FUNCTION dbo.GetNumericValue
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
-- For Alphbet values
Create FUNCTION dbo.GetAlphaValue
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
--- select statement
select dbo.GetNumericValue(columnTextNumbers) as columnNumberValues, dbo.GetAlphaValue(columnTextNumbers) as columnTextValues from AlphNumeric

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Accepted answer
Hi,

You can use below function to get desire values, If you wish you can create single function with one more parameter which will take eithere numericvalues or alpha value
-- For Numeric values
CREATE FUNCTION dbo.GetNumericValue
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

-- For Alphbet values
Create FUNCTION dbo.GetAlphaValue
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

--- select statement
select dbo.GetNumericValue(columnTextNumbers) as columnNumberValues, dbo.GetAlphaValue(columnTextNumbers) as columnTextValues from AlphNumeric