Parsing data... - 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

General SQL Server Forums

New to SQL Server Programming

Parsing data...

Author

Topic

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 13:37:08

Table1 includes a column for email address. I'd like to parse everything up to 8 chars before the '@' symbol in this column. My script doesn't appear to be working, and I'd appreciate any assistance in troubleshooting it!

select substring(UPPER(substring(email,1,charindex('@',email)-1)),1,8)
from table1

The error produced is;

Invalid length parameter passed to the substring function.

What am I doing wrong? The logic in the script looks good to me...

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-05 : 14:02:37

declare @table1 table (
email varchar(100)
)

insert into @table1
select 'qwertyuiopasd@me.com' union
select 'qwertyuiopas@me.com' union
select 'qwertyuiopa@me.com' union
select 'qwertyuiop@me.com' union
select 'qwertyuio@me.com' union
select 'qwertyui@me.com' union
select 'qwertyu@me.com' union
select '' union
select 'qwertyuiopasdfghjkl'

select * from @table1

select substring(UPPER(substring(email,1,charindex('@',email)-1)),1,8)
from @table1
where email like '%@%'

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-03-05 : 14:31:26

That is a very round-about method of doing this, but hey - it works!

Thanks so much :)

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-03-05 : 14:33:30

Round about???
That is a working example.
Your error was in selecting records that did not contain @

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-05 : 22:23:01

Correct... the INSERT/SELECT is just to load sample data to show you how to do it.

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 07:54:39

quote:
Originally posted by Jeff Moden

Correct... the INSERT/SELECT is just to load sample data to show you how to do it.

--Jeff Moden

Thats why mostly I give generalised suggestion like

select columns from your_table
where col like........ (etc)

Madhivanan

Failing to plan is Planning to fail

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-03-06 : 08:38:22

My mistake - I interpretted the initial response to include building a temp table. If that portion can be disregarded, then its all pretty straight forward :)

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-06 : 08:56:39

That's exactly right.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources