Split Function Logic Required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Split Function Logic Required

Author

Topic

vijay1234
Starting Member

48 Posts

Posted - 2014-03-16 : 02:38:30

Hello All,

I have created an user defined function with one input parameter. The input parameter is a string (varchar) which uses spaces,comma & '.' at any index level ( starting, ending or middle ). As per the business req i have put some conditions and are below.

1)String starting with '-','.',space (' '), characters should be replaced with NULL ('') which means no space should occur and the rest portion of the string should start from the first index. (Ex:_KaranPratap -->KaranPratap )
2) String using specific words at any stage, used wild card ( like operator) should result as 'Audio' as ouput.
3) String using specific words at any stage, used wild card ( like operator) should result as 'Unknown' as ouput.
4) String starting with '.' should appear as 'Unknown' as ouput.
5) If at all the string is not having any spaces, then the complete string is considered as first part and the same will be resulted as ouput.
6) If there multiple spaces in the string then the firt substring (first space before), will be resulted as the ouput.
6) If there is any email string with '@' then the pre portion of '@' is to be resulted. (Ex:karanpratap@aol.com --> karanpratap ), if not then the complete string
will be considered as first part and will be resulted.

The function is used in one of the stored procedures where EPN, the input parameter is being called joining with a view.

The results are below as per the above conditions

PN EPN
User123 User123 user0041
UnKnown .JH.123.BNM_SDX.2345678
UnKnown 123
test test@aol.com
Karan Karan Pratap Singh ---> (Same should come )
Karan€™s Karan€™s iPhone

Here is the function created

CREATE FUNCTION [dbo].[fn_xyz](@EPN VARCHAR(200))
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE @PN VARCHAR(200)
DECLARE @FirstPart VARCHAR(200)

-- Condtions --

IF LEFT(@EPN,1) = '-'
SET @EPN = REPLACE(@EPN,'-','')
IF LEFT(@EPN,1) = '.'
SET @EPN = REPLACE(@EPN,'.','')
IF LEFT(@EPN,1) = ' '
SET @EPN = SUBSTRING(@EPN,2,LEN(@EPN)-1)

IF @EPN LIKE '%ABS78%' OR @EPN LIKE '%DFS%'
BEGIN
SET @PN='Audio'
END
ELSE IF @EPN LIKE '%ABS_DFS%'
BEGIN
SET @PN='UnKnown'
END
ELSE IF ISNUMERIC(REPLACE(@EPN,'.','')) = 1
BEGIN
SET @PN='UnKnown'
END
ELSE

BEGIN
--If there is no space in the string then consider full string as first part
IF CHARINDEX(' ',@EPN) = 0
SET @FirstPart=@EPN
ELSE
--Extract data before first space as First part
SET @FirstPart=LEFT(@EPN,CHARINDEX(' ',@EPN))

IF @FirstPart LIKE '%@%'
--Extract data before @ in first part as PN
SET @PN = LEFT(@FirstPart,CHARINDEX('@',@FirstPart)-1)

ELSE
--If there is no @ in first part consider first part as PN
SET @PN = @FirstPart

END
set @PN= case when isnumeric(@PN) = 1 then 'UnKnown' else @PN end
RETURN @PN
END

What i am looking for is for a logic where the result should be displayed using some split function.
Desired Result
For Ex:
Karan Pratap Singh --> Result: Karan Pratap Singh
Karan User@aol.com --> Result: karan
Karan€™s --> Result: Karan€™s iPhone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources