Right allinging text - 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

Right allinging text

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-10-03 : 06:04:30

LEts say i have a 6 digit number , but to to be stored
in a right alligned way ie:
" 123456" as opposed to : "123456 "

but id need it also to incorporate the blanks on the left
with a 7 or 8 dig number ..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 06:11:58

[code]select right(replicate(' ', 8) + convert(varchar(6), 123456), 8) as num[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 06:18:54

Can this be done in front-end?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 06:34:08

quote:
Originally posted by pazzy11

LEts say i have a 6 digit number , but to to be stored
in a right alligned way ie:
" 123456" as opposed to : "123456 "

but id need it also to incorporate the blanks on the left
with a 7 or 8 dig number ..

Dont store formatted data in a table
Store proper data
It is your front end application where you need to format it the way you want

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-10-03 : 06:57:55

yes exactly, it is a write SP that modifies table data, and writes to a txt file,
no table data is being permanently changed,
i have this now
[CODE]select CAST(ISNULL(right(replicate(' ', 8) + convert(varchar , 123456), 9),' ')) as CHAR(9)
[/CODE]

and am getting the syntax error Incorrect syntax near 'CAST', expected 'AS'.
?? i dont understand ,, this is properly indented and ISNULL is receiving proper args

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-03 : 07:00:46

your bracket is in the wrong place...

select CAST(ISNULL(right(replicate(' ', 8) + convert(varchar , 123456), 9),' ') as CHAR(9))

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 07:01:13

select CAST(ISNULL(right(replicate(' ', 9) + convert(varchar , 123456), 9),' ') as CHAR(9))

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 07:07:37

Also, it will be nice if you explicitly define size for Varchar field rather than accepting default.

select CAST(ISNULL(right(replicate(' ', 9) + convert(varchar(10) , 123456), 9),' ') as CHAR(9))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 07:11:24

I recommend not relying on the default length of VARCHAR. This will work for a casting a number, but not, for example, a GUID!


SELECT	convert(varchar , CONVERT(uniqueidentifier, '92F1CA7E-DBC2-4D99-82C9-3B497B095A83'))
SELECT	convert(varchar(36), CONVERT(uniqueidentifier, '92F1CA7E-DBC2-4D99-82C9-3B497B095A83'))

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 07:27:57

quote:
Originally posted by Kristen

I recommend not relying on the default length of VARCHAR. This will work for a casting a number, but not, for example, a GUID!

SELECT convert(varchar , CONVERT(uniqueidentifier, '92F1CA7E-DBC2-4D99-82C9-3B497B095A83'))
SELECT convert(varchar(36), CONVERT(uniqueidentifier, '92F1CA7E-DBC2-4D99-82C9-3B497B095A83'))

Kristen

IMO, it should throw the error if the length is not specified

See the difference on how sql server takes the length for varchar if length is not specified


--case 1
Declare @v varchar
set @v='this is the text that exceeds thity characters'
select @v

--case 2

select convert(varchar,'this is the text that exceeds thity characters')

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 07:43:45

Its even more daft because the CAST gives you 30 characters, which covers all conversions (I think) except the GUID which presumably came in a later version ... and the you only get ONE for anything other than a Cast. Useless ...

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 07:44:07

p.s. I think that "feature" should be deprecated

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 09:35:38

quote:
Originally posted by Kristen

p.s. I think that "feature" should be deprecated

Yes. From next versions onward, I expect it to throw error

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-10-03 : 09:45:37

problem with this is it wont right allign it if it is already left alligned
ie
[CODE]
select CAST(ISNULL(right(replicate(' ', 9) + convert(varchar , '12548 '), 9),'') as CHAR(9)) as x
[/CODE]
this doesn't return it completely right alligned .. ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-03 : 09:48:15

[code]select CAST(ISNULL(right(replicate(' ', 9) + convert(varchar , rtrim('12548 ')), 9),'') as CHAR(9)) as x[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources