Padding string - 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

Padding string

Author

Topic

ymamalis
Starting Member

42 Posts

Posted - 2010-07-03 : 03:56:42

Hello to everybody. i want to format the result of the below query in a format of 40 characters(padding right with ' ') , 7 characters padding left with ' ' and 10 characters padding left with ' '
the first 40 should be from ISNULL(eponymo,'') + ' ' + ISNULL(onoma,'-')
the 7 from CONVERT(NVARCHAR, kvd_pelath)
and the 10 from ISNULL(CONVERT(NVARCHAR, hmer_genhshs, 103),'')
but unfortunatelly i am getting some null results

the query is
SELECT id_ontotita AS ObjectID,
ISNULL(eponymo,'') + ' ' + ISNULL(onoma,'-') + ' ' + CONVERT(NVARCHAR, kvd_pelath) +' - ' + ISNULL(CONVERT(NVARCHAR, hmer_genhshs, 103),'')AS Description
FROM ontotita

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-07-03 : 04:34:17

Might be kvd_pelath column having NULL values. Concatenation with NULL will result as NULL

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

ymamalis
Starting Member

42 Posts

Posted - 2010-07-03 : 05:04:10

no there is no chance to have kvd_pelath null because it is a keyfield of the table. can you send me the fucntion you will use to have the proper result please? i user replicate but no luck
thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-03 : 05:53:30

[code]
SELECT id_ontotita AS ObjectID,
ISNULL(eponymo,'') + ' '
+ ISNULL(onoma,'-') + ' '
+ ISNULL(CONVERT(NVARCHAR, kvd_pelath), '') + ' - '
+ ISNULL(CONVERT(NVARCHAR, hmer_genhshs, 103), '') AS Description
FROM ontotita
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2010-07-03 : 06:58:44

sorry i didn;t make my problem clear . i need ot have result is formatted way
40 characters for the first 2 columns if the length is less then add some spaces 7 for the kvd_pelth and 10 for the last one

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-03 : 08:52:59

something like this


LEFT(ISNULL(eponymo,'') + ' ' + ISNULL(onoma,'-') + SPACE(40), 40)

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2010-07-03 : 10:06:05

i tried that
SELECT id_ontotita AS ObjectID,
LEFT(ISNULL(eponymo,'') + ' ' + ISNULL(onoma,'-') + SPACE(40), 40)+ ' ' + right(CONVERT(NVARCHAR, kvd_pelath)+space(7),7) +' - ' + right(ISNULL(CONVERT(NVARCHAR, hmer_genhshs, 103),'')+space(10),10)AS Description
FROM ontotita

but i get wrong results

ymamalis
Starting Member

42 Posts

Posted - 2010-07-03 : 10:07:58

i am getting that results
223 ?????????S G?O?G??S 1016 - 13/01/1974
224 ??????S G?????S 1006 - 22/03/1972
225 ????S ???S??S 6012 - 24/02/1972
227 ??S?????S ???S 100 - 31/07/1971
228 S???????????S ?OS??S 7800 - 12/08/1969
229 ???????S G?O?G?S 8000 - 03/05/1967

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-04 : 03:48:09

what is wrong with that result ?

KH
[spoiler]Time is always against us[/spoiler]

ymamalis
Starting Member

42 Posts

Posted - 2010-07-04 : 07:14:46

i want ot take it is fixed columns like.
40 characters 1 st column , 7 characters 2nd column , 10 characters last column and all that should be only one string with total lenght 57 characters!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources