Data Filtering and cleaning - 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

Data Filtering and cleaning

Author

Topic

sandaire
Starting Member

5 Posts

Posted - 2009-01-27 : 10:13:25

Hello:
I have a string of data: joeCharles@blowfish.com or blowfish\joeBlow or blowfish/joeBlow.

I would like to grab only the data that precedes the "@" symbol or that supercedes the "/" or "\" slashes.

Is there a way to do this in SQL?

Tks,

Johnny

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-27 : 10:47:45

Many ways. The best is to incorporate the logic in whatever process populates the database so that the values are clean when they get in there. But here is one possible way for the samples you gave:


select substring(s, begIndex, isNull(endindex-1, l))
from   (
       select nullif(charindex('@', s),0) endindex
              ,len(s) l
              ,charindex('\', replace(s, '/', '\')) + 1 begIndex
              ,s
       from   (
              select 'joeCharles@blowfish.com' s union all
              select 'blowfish\joeBlow' union all
              select 'blowfish/joeBlow' union all
              select 'blowfish'
              ) d
       ) d

output:
-----------------------
joeCharles
joeBlow
joeBlow
blowfish

EDIT:
sorry, the "supercedes" confused me

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:49:34

if your string format is always either one of the above three, you can use this

SELECT COALESCE(PARSENAME(REPLACE(REPLACE(REPLACE(col,'@','.'),'/','.'),'\','.'),3),PARSENAME(REPLACE(REPLACE(REPLACE(col,'@','.'),'/','.'),'\','.'),1)) FROM Table

sandaire
Starting Member

5 Posts

Posted - 2009-01-27 : 16:27:58

In the reference that I posted, I am not sure what the strings will be. What I know is that they will come to me with either the "@" symbol or the forward "/" or backslash "\". I would like to parse the string and keep the string that is in front of the "@" symbol and the string that is after the "/" or "\" slashes. Can I use the charindex? What would be the most effective manner to parse the string?

Tks,

Johnny

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-27 : 17:35:18

After I changed my orig post to fit your requirements you have 2 ways to do what you're asking. In case you didn't realize, visakh16 used a table named [Table] which you would need to replace with your table along with the column name which he named [col]. I used a derived table [d] and the column [s]. Change either of our code snippets to match your table/column names

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-27 : 17:51:52

[code]select
s,
New_S =
convert(varchar(40),
case
when s like '%@%'
then parsename(replace(s,'@','.'),2)
when s like '%\%'
then parsename(replace(s,'\','.'),1)
when s like '%/%'
then parsename(replace(s,'/','.'),1)
else s
end)
from
( --Test Date
select 'joeCharles@blowfish.com' s union all
select 'blowfish\joeSmith' union all
select 'blowfish/joeJones' union all
select 'blowfisher'
) a

Results:
s New_S
----------------------- ----------------------------------------
joeCharles@blowfish.com blowfish
blowfish\joeSmith joeSmith
blowfish/joeJones joeJones
blowfisher blowfisher

(4 row(s) affected)[/code]

CODO ERGO SUM

sandaire
Starting Member

5 Posts

Posted - 2009-01-27 : 20:52:44

I found another method...
I used the Select @MyNewValue = LEFT(PATINDEX(%@%,'MyString@Domain.com'),-1)
for the others, I use the RIGHT with a +2.
That approach does the trick.

Thank you for the pointer to CHARINDEX, which is I found the string functions in the SQL Help.

Regards,

Johnny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources