Charindex Space

[twifosp]

I'm running into a wierd bug that I can't figure out.

I have a column that stores email addresses. For whatever reason, the person who designed this application decided to make it store the email address twice in the same column.

So the data looks like: email@email.com email@email.com

Should be simple to parse right?

select	charindex(' ', from) 
from 	table

Only that returns 0's for all the rows.

If I COPY the email out of the SQL results and do it manually, it works fine.

select charindex(' ', 'email@email.com email@email.com')
returns: 16
Just like it should.

So it's a data type issue perhaps? Well the data type in the table is nvarchar, and I've tried converting or casting it to varchar or char inside of the charindex and still no dice.

I can't figure this out... anyone have any ideas? Could there be a hidden soft return or some kind of wierd data bit that is stored in the column where the space is showing? How do I search for something like that?

 

[Katy44]

The thing I noticed and this might be completely not the prob is that 'from' is a reserved word, you need to put it in square brackets in your query.

 

[JSpicolli]

DECLARE @EmailString as Varchar(255)
DECLARE @SplitChar int
SET @EmailString = 'email@email.com email@email.com'


SELECT @SplitChar = PATINDEX('% %', @EmailString)

SELECT LEFT(@EmailString, @SplitChar)

 

[twifosp]

Katy44 -- The column name is actually tFrom.... I just simplified it for this post. Good thought tho.

JSpicolli -- while this might work, I need to perform this operation on over 3 million rows.

Also, why would filling a variable with the information be any different than performing the operating on the column itself?

Any other ideal solutions?

 

[SQLBill]

One thing to check:

select charindex(' ', 'email@email.com email@email.com')
returns: 16
Just like it should.

Yes, it should...but that might NOT be the way the data is stored. If the data is actually stored with a space in front of BOTH email addresses:

' email@email.com email@email.com'

then the query will return 0. Which is what you are getting.

Try this....

SELECT 'A' + columnname
FROM tablename

Replace the word columnname with the real column. That will return:

Aemail
or
A email

depending on whether the email value starts with a space or not.

-SQLBill

Posting advice: FAQ481-4875

 

[twifosp]

Interesting development however.

When I do pass the column into a variable, and print the variable I get:
email@email.com
email@email.com

instead of:
"email@email.com email@email.com"

So this means it's a soft return? How do I search for a soft return in the column?

 

[twifosp]

Bill -- Good idea, but I tested it for spaces before hand. Your test reveals it shows up as "Aemail@email.com email@email.com"

I think I've isolated it to beinga soft return appearing as a space in the middle of the two addresses. I just can't figure out how to parse it.

 

[SQLBill]

I've never done this and am not quite sure how to do it....but you can use the ASCII value with CHARINDEX and other commands. Might want to do a search for ASCII on this forum. Also, keywords of return or carriage return might help.

-SQLBill

Posting advice: FAQ481-4875

 

[mercwrought]

how does

select    charindex(char(13), tfrom) from     table

work?

 

[twifosp]

mercwrought -- the table doesn't just contain one email so unfortunetly that won't work.

 

[mercwrought]

mabey im not all here today but what cant you parse the column with

select    left(tfrom ,charindex(char(13),tfrom )from table