How do I parse text to tell sql the character len? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 Administration

How do I parse text to tell sql the character len?

Author

Topic

robert693
Starting Member

42 Posts

Posted - 2009-08-06 : 14:46:59

Hello,

I have a table called External that has fields called First_Name, Last_Name, Fax, and Email. Email is a Varchar(40) data type. I need to relplace wha is in the Email field with a code that works with a program that will fax people listed in the External table. A person here helped me get the following code:

UPDATE [External] SET EMail = ('RFAX: '+LEFT(First_Name,1)+', '+LEFT(Last_Name,15)+', '+Suffix+'@/FN='+CASE WHEN LEFT(Fax,1) <> '1' THEN '1' ELSE '' END + Replace(Replace(Replace(Replace(Fax,'(',''),')',''),'-',''),'/',''));

The fax number should have no formatting or puncuation after it is dumped, with the rest of the text, into the email field. In other words, (309) 555-2626 becomes 13095552626.
This will work except that if I use the above code, this is what happens:
'RFAX: ' = 6 characters
LEFT(First_Name,1) = 1 character
', ' = 2 characters
LEFT(Last_Name,15) = 15 characters
', ' = 2 characters
Suffix = 13 characters
'@/FN=' = 5 characters
CASE WHEN LEFT(Fax,1) = 1 or 0 characters
Fax = 25 characters

That’s a total of 70 characters. Unless Fax and Suffix are parsed to include not all characters, SQL will assume that all characters could possibly be used. Since the Email field is a 40 character field, I need to parse the text going into it to make sure that it is no mre than 40 characters. If I could get some help with this I would greatly appreciate it!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-06 : 14:51:07

See LEN() function

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-06 : 15:52:06

Perhaps this. It keeps the entire fax number and truncates the stuff before it:


with [external]
as
(
select first_name = 'Wizard'
       ,Last_name = 'Of Oz'
       ,Suffix = 'The Great and wonderfull'
       ,fax = '(800) 123-4567'
)
--UPDATE [External] SET 
select
       EMail = left(
             'RFAX: '+LEFT(First_Name,1)
              + ', '+ LEFT(Last_Name,15)
              + ', ' + Suffix 
              , 40 - len('@/FN=' + CASE WHEN LEFT(Fax,1) <> '1' THEN '1' ELSE '' END  + replace(Replace(Replace(Replace(Replace(Fax,'(',''),')',''),'-',''),'/',''),' ','')))
              + '@/FN=' + CASE WHEN LEFT(Fax,1) <> '1' THEN '1' ELSE '' END + replace(Replace(Replace(Replace(Replace(Fax,'(',''),')',''),'-',''),'/',''),' ','')
from [external]

OUTPUT:
EMail
---------------------------------------------
RFAX: W, Of Oz, The Grea@/FN=18001234567

Be One with the Optimizer
TG

robert693
Starting Member

42 Posts

Posted - 2009-08-07 : 10:32:07

I need to tell SQL to ignore the the characters that are taken out, punctuation and formatting in the Fax and just count the fax in the form: 13095552444.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 10:39:41

That is what my code does. Did you try it???


select len('RFAX: W, Of Oz, The Grea@/FN=18001234567')

output:
40

Be One with the Optimizer
TG

robert693
Starting Member

42 Posts

Posted - 2009-08-07 : 13:49:49

I apologize!!! It did work! Thank you very much for all of your help! And thank you for your patience with an SQL newbie!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-07 : 14:10:21

no worries

you're welcome

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources