adding Leading Spaces to text based of length

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)

adding Leading Spaces to text based of length

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2005-11-17 : 11:33:10

Hi all,

I'm wondering if this is possible. I want need to add Leading Spaces to Column A, based on how long the value is in that column.

If the text is 4 characters long I need to add 5 spaces in front, if the text is 5 characters long I need to add 4 spaces in front, and so on.

There are thousands of rows, so I'm trying to see if this is something I run in QA quickly. Your thoughts?

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-17 : 11:44:09

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(9))
GO

INSERT INTO myTable99(Col1)
SELECT 'a' UNION ALL
SELECT 'ab' UNION ALL
SELECT 'abc' UNION ALL
SELECT 'abcd' UNION ALL
SELECT 'abcde' UNION ALL
SELECT 'abcdefg' UNION ALL
SELECT 'abcdefgh' UNION ALL
SELECT 'abcdefghi'
GO

SELECT RIGHT(REPLICATE(' ',9) + Col1,9) FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

hueby
Posting Yak Master

127 Posts

Posted - 2005-11-17 : 12:06:47

Oh cool! That is exactly want I was wanting to accomplish. THANK YOU

hueby
Posting Yak Master

127 Posts

Posted - 2005-11-17 : 12:14:22

...now what is the best way to actually execute this on the table?

Kristen
Test

22859 Posts

Posted - 2005-11-17 : 13:17:59

At a guess:

UPDATE U
SET Col1 = RIGHT(REPLICATE(' ',9) + Col1,9)
FROM myTable99 AS U

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 23:35:34

Assuming that your original column is of varchar type

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-18 : 15:28:03

Sometimes I feel bad when I give out "advice" like this...so I have a margarita...

Why do you want to store data in that manner? It's gonna be more of hassle then I think you realize.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

hueby
Posting Yak Master

127 Posts

Posted - 2005-11-18 : 15:34:02

Hi all, I ending up using:

UPDATE VN_vendor_master_mc
SET VN_vendor_master_mc.vendor_code = RIGHT(REPLICATE(' ',10) + vendor_code,10)
where VN_vendor_master_mc.Company_code = 'HWP'

Works great. THe only reason I had to do this, was because the software we use requires this. I was only doing a data dump and needed to format the columns so the software would take it.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-18 : 16:23:32

I hate 3rd party vendors...

What possible purpose does the vendor require you to do this to the data for?

What's the name of the product?

It's Friday and I could use a good laugh...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

hueby
Posting Yak Master

127 Posts

Posted - 2005-11-18 : 16:27:04

It is ForeFront by Dexter & Chaney. It is Job Costing / Accounting software for the construction industry.

It has some quirks to it...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-18 : 17:32:25

In my experience, Account software is some of the worst designed database applications out there. Consistently bad. As if it was designed by accountants or something...

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 19:48:20

"It has some quirks to it... "

"As if it was designed by accountants or something..."

Fantastic! Great way to end the week. Thanks guys!

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-18 : 20:02:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources