Urgent: Split Function with Space as Demiliter

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)

Urgent: Split Function with Space as Demiliter

Author

Topic

vijay1234
Starting Member

48 Posts

Posted - 2014-03-13 : 21:52:51

Hi,
I have a scenario where i am currently working Please find below the details.

> I'm calling a function from storedprocedure in my scenario,

I would want the below function to be modified as per the new logic mentioned at last.

//** Function **//

ALTER FUNCTION [dbo].[fn_XYZ]
(
@EndpointName VARCHAR(200)
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Participant VARCHAR(200)
DECLARE @FirstPart VARCHAR(200)
--Eliminating -, .,space if exists in begining of string
IF LEFT(@EndpointName,1) = '-'
SET @EndpointName = REPLACE(@EndpointName,'-','')
IF LEFT(@EndpointName,1) = '.'
SET @EndpointName = REPLACE(@EndpointName,'.','')
IF LEFT(@EndpointName,1) = ' '
SET @EndpointName = SUBSTRING(@EndpointName,2,LEN(@EndpointName)-1)

---Conditions--

IF @EndpointName LIKE '%STC99%' OR @EndpointName LIKE '%STG%'
BEGIN
SET @Participant='Audio'
END
ELSE IF @EndpointName LIKE '%DMA_VMR%'
BEGIN
SET @Participant='UnKnown'
END
ELSE IF ISNUMERIC(REPLACE(@EndpointName,'.','')) = 1
BEGIN
SET @Participant='UnKnown'
END
ELSE
BEGIN
--If there is no space in the string then consider full string as first part
IF CHARINDEX(' ',@EndpointName) = 0
SET @FirstPart=@EndpointName
ELSE
--Extract data before first space as First part
SET @FirstPart=LEFT(@EndpointName,CHARINDEX(' ',@EndpointName))

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

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

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

2) After query run

select distinct dbo.fn_XYZ(EndpointName) Participant,EndpointName from dbo.vw_ABCD
order by Participant desc

Results are as below:

Participant EndpointName
UnKnown 123 --- As per the above condition --
User1 User1 user5724 --- As per the above condition --
Krupa€™s Krupa€™s BlackBerry --- As per the above conditions --
Varun Varun Malhothra Singh -- Require full name ---

Required:
Varun Malhothra Singh as it is (with spaces) as 'Participant' with out making any changes to my above given conditions.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-14 : 14:03:50

I'm not sure what your question is. Here are a couple of links that can help you to present your question with more detail and with sample data so we can help you better:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

vijay1234
Starting Member

48 Posts

Posted - 2014-03-14 : 23:25:13

Hello,
I’ve created a function to eliminate spaces ( Comma or Fullstop ) at the first character of the string and set that to NonSpace by using REPLACE function. And have used multiple conditions, if the string is using
‘STC99’ or ‘STG’ any where, should set to ‘ AUDIO’ and set to ‘UNKNOWN’ if the String is staring with ‘.’ Or Numeric Characters Etc.. If there is no space in the string then considered full string as first part. Similarly, if there is any space in between the characters of the string then Extract data before first space as First part ( Ex: Varun Malhothra Singh ? Then ‘Varun’ as Participant Name). Similarly, if there is any ‘Email’ then, Extract data before @ in first part as participant ( varunmalhothra@gmail.com ?then ‘varunmalhothra’ as participant name).If there’s no @ in the string then considered the firstname as the participant name.
As per the used conditions my result set is as below

Participant EndPointName
User123 User123 user0041
UnKnown .ID.123.SVO_BNV.14510004
Unknown 12345
Karan€™s Karan€™s iPad chucked.india@xyz.com

Requirement:
My requirement is to display the Endpoint name ( with spaces ) as Participant Name using SplitFunction.
Without making any changes to my above given conditions.

Participant ENdPointName

Varun Malhothra Singh Varun Malhothra singh

Here's my Code below:

//** Function **//
Create FUNCTION [dbo].[fn_XYZ](@EndpointName VARCHAR(200))
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @Participant VARCHAR(200)
DECLARE @FirstPart VARCHAR(200)
IF LEFT(@EndpointName,1) = '-'
SET @EndpointName = REPLACE(@EndpointName,'-','')
IF LEFT(@EndpointName,1) = '.'
SET @EndpointName = REPLACE(@EndpointName,'.','')
IF LEFT(@EndpointName,1) = ' '
SET @EndpointName = SUBSTRING(@EndpointName,2,LEN(@EndpointName)-1)
IF @EndpointName LIKE '%STC99%' OR @EndpointName LIKE '%STG%'
BEGIN
SET @Participant='Audio'
END
ELSE IF @EndpointName LIKE '%DMA_VMR%'
BEGIN
SET @Participant='UnKnown'
END
ELSE IF ISNUMERIC(REPLACE(@EndpointName,'.','')) = 1
BEGIN
SET @Participant='UnKnown'
END
ELSE
BEGIN
IF CHARINDEX(' ',@EndpointName) = 0
SET @FirstPart=@EndpointName
ELSE
SET @FirstPart=LEFT(@EndpointName,CHARINDEX(' ',@EndpointName))

IF @FirstPart LIKE '%@%'
SET @Participant = LEFT(@FirstPart,CHARINDEX('@',@FirstPart)-1)
ELSE
SET @Participant = @FirstPart
END
set @Participant= case when isnumeric(@participant) = 1 then 'UnKnown' else @participant end
RETURN @Participant
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources