Removeing periods and spaces in varchar

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 2000 Forums

SQL Server Development (2000)

Removeing periods and spaces in varchar

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-08 : 10:02:20

I need to create a S.P. that will take a Varchar Field and remove all the spaces and periods between the letters then crop leaving only the left 3 letters. Any Ideas?

Jim

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-08 : 10:15:35

Look up REPLACE and LEFT in books online.

Jay White
{0}

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-08 : 10:44:00

AAAAAAAAAAAAAAAhhhhhhhhhhhhhhhhhhAaaaaaa

UPDATE dbo.MaCust
SET Old_Cust_ID = [Cust ID], Conv_Cust_name = Name
UPDATE dbo.MaCust
SET Conv_Cust_name = REPLACE(Conv_Cust_name, ' ', '')
UPDATE dbo.MaCust
SET Conv_Cust_name = REPLACE(Conv_Cust_name, '-', '') UPDATE dbo.MaCust
SET Conv_Cust_name = REPLACE(Conv_Cust_name, '.', '')
UPDATE dbo.MaCust
SET Conv_Cust_name = LEFT(Conv_Cust_name, 3)

Thanks

Jim

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-08 : 21:11:33

JimL,

You can use the output of a function as the input to another.

UPDATE dbo.MaCust
SET
Old_Cust_ID = [Cust ID],
Conv_Cust_name = LEFT(REPLACE(REPLACE(REPLACE(Name,'',''),'-',''),'.',''),3)

This is also a single transaction.

Edited by - ValterBorges on 05/08/2003 21:13:12

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-09 : 09:21:36

Thanks Valter

That does save alot of typeing.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-09 : 10:25:06

Where is that UDF from Anronold?

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-09 : 13:00:19

A Crappy old As400 Accounting system in RPG2, That's I have to convert to SQL.

I think I will Be posting Alot the next 2 months.

Oh well at least it raises my counts.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources