SubString to be Eliminated - 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)

SubString to be Eliminated

Author

Topic

vijay1234
Starting Member

48 Posts

Posted - 2014-04-10 : 02:44:32

Hi,

How to remove Email portion in the string

Scenario 1:

VijayMalhothra@aol.com VijayMalhothra@aol.com

Expected Result: VijayMalhothra

Scenario 2:

XYZ VijayMalhothra@aol.com

Expected Result: XYZ

In Both the cases i would want to eliminate the second substring after space(delimiter) which contains '@'.

I want this to be used in a function.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-04-10 : 04:31:20

select substring(col,1,charindex('@',col)-1) from yourtable

Madhivanan

Failing to plan is Planning to fail

vijay1234
Starting Member

48 Posts

Posted - 2014-04-10 : 04:48:36

Hi,

DECLARE @Str VARCHAR(100) = 'vijayverizon vijay@aol.com'
select substring(@str,1,charindex('@',@Str)-1)

Result: vijayverizon vijay

I would want only the firstpart that is vijayverizon not the second substring before '@'

Any thoughts please ? which should obey both my above 2 scenarios

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-10 : 04:53:43

Here it is......


CREATE FUNCTION EmailParts(@Name VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
		DECLARE @InputString VARCHAR(MAX) = @Name 
		DECLARE @StringPart VARCHAR(MAX)= NULL
		IF( @InputString IS NOT NULL)
			
			SET @StringPart =( SELECT LEFT(@InputString,CHARINDEX(' ',@InputString)-1) )
				IF CHARINDEX('@',@StringPart)<>0
					SET @StringPart = (SELECT LEFT(@StringPart,CHARINDEX('@',@StringPart)-1) )
				
					RETURN @StringPart
END

SELECT dbo.EmailParts('XYZ VijayMalhothra@aol.com')

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-10 : 05:44:07

If it is not so Try this.....
This obeys your both conditions for sure


CREATE FUNCTION EmailParts(@Name VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
		DECLARE @InputString VARCHAR(MAX)  = @Name 
		DECLARE @StringPart1 VARCHAR(MAX)  = NULL
		DECLARE @StringPart2 VARCHAR(MAX)  = NULL
		DECLARE @ResultString VARCHAR(MAX) = NULL
		IF( @InputString IS NOT NULL)
					SET @StringPart1 = (SELECT LEFT(@InputString,CHARINDEX(' ',@InputString)-1))
					SET @StringPart2 = (SELECT SUBSTRING(@InputString,CHARINDEX(' ',@InputString),LEN(@InputString)))
				IF (CHARINDEX('@',@StringPart1)<>0 AND  CHARINDEX('@',@StringPart2)<>0)
					 BEGIN
						SET @StringPart1 = (SELECT LEFT (@StringPart1,CHARINDEX('@',@StringPart1)-1) )
						SET @StringPart2 = (SELECT LEFT(@StringPart2,CHARINDEX('@',@StringPart2)-1) ) 
						SET @ResultString = @StringPart1+' '+@StringPart2
					 END
				ELSE  IF (CHARINDEX('@',@StringPart1)<>0)
					 BEGIN
						SET @StringPart1 = (SELECT LEFT (@StringPart1,CHARINDEX('@',@StringPart1)-1) )
						
						SET @ResultString = @StringPart1+' '+@StringPart2
					 END
				ELSE IF CHARINDEX('@',@StringPart2)<>0 
					BEGIN
						SET @StringPart2 = (SELECT LEFT(@StringPart2,CHARINDEX('@',@StringPart2)-1) ) 
						SET @ResultString = @StringPart1+' '+@StringPart2
					END 
				ELSE IF (CHARINDEX('@',@StringPart1)=0 AND CHARINDEX('@',@StringPart2)=0)
					 BEGIN
						SET @ResultString = @StringPart1+' '+@StringPart2
					 END 
					RETURN @ResultString
END

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

vijay1234
Starting Member

48 Posts

Posted - 2014-04-10 : 06:15:53

Hi,

Thankyou v much murali for prompt revert :)

--CREATE FUNCTION EmailParts(@Name VARCHAR(MAX))
--RETURNS VARCHAR(MAX)
--AS
--BEGIN
DECLARE @InputString VARCHAR(MAX) = 'xyz verizon@aol.com'
DECLARE @StringPart1 VARCHAR(MAX) = NULL
DECLARE @StringPart2 VARCHAR(MAX) = NULL
DECLARE @ResultString VARCHAR(MAX) = NULL
IF( @InputString IS NOT NULL)
SET @StringPart1 = (SELECT LEFT(@InputString,CHARINDEX(' ',@InputString)-1))
SET @StringPart2 = (SELECT SUBSTRING(@InputString,CHARINDEX(' ',@InputString),LEN(@InputString)))
IF (CHARINDEX('@',@StringPart1)<>0 AND CHARINDEX('@',@StringPart2)<>0)
BEGIN
SET @StringPart1 = (SELECT LEFT (@StringPart1,CHARINDEX('@',@StringPart1)-1) )
SET @StringPart2 = (SELECT LEFT(@StringPart2,CHARINDEX('@',@StringPart2)-1) )
SET @ResultString = @StringPart1+' '+@StringPart2
END
ELSE IF (CHARINDEX('@',@StringPart1)<>0)
BEGIN
SET @StringPart1 = (SELECT LEFT (@StringPart1,CHARINDEX('@',@StringPart1)-1) )

SET @ResultString = @StringPart1+' '+@StringPart2
END
ELSE IF CHARINDEX('@',@StringPart2)<>0
BEGIN
SET @StringPart2 = (SELECT LEFT(@StringPart2,CHARINDEX('@',@StringPart2)-1) )
SET @ResultString = @StringPart1+' '+@StringPart2
END
ELSE IF (CHARINDEX('@',@StringPart1)=0 AND CHARINDEX('@',@StringPart2)=0)
BEGIN
SET @ResultString = @StringPart1+' '+@StringPart2
END
--RETURN @ResultString
select @ResultString

Result :xyz verizon

But what i'm looking for only xyz to be displayed. f there is email part in second substring after space then only first substring needs to be displayed. Again if there is @ in first substring then the before portion of '@' needs to be displayed as result.

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-10 : 06:28:04

Hi Vijay....
Observer my first solution hope it will help you..
If your problem is still not resolved then post all conditions properly..

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

vijay1234
Starting Member

48 Posts

Posted - 2014-04-10 : 06:35:46

Thanks Murali.

I haven't seen your first post. Apologies.

It is absolutely awesome.

DECLARE @InputString VARCHAR(MAX) = ('XYZ@aol.com VijayMalhothra@aol.com')
DECLARE @StringPart VARCHAR(MAX)= NULL
IF( @InputString IS NOT NULL)

SET @StringPart =( SELECT LEFT(@InputString,CHARINDEX(' ',@InputString)-1) )
IF CHARINDEX('@',@StringPart)<>0
SET @StringPart = (SELECT LEFT(@StringPart,CHARINDEX('@',@StringPart)-1) )

SELECT @StringPart

Result: XYZ

Thank you so much Murali :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources