String CAST and spacing - 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

SQL Server 2000 Forums

SQL Server Development (2000)

String CAST and spacing

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2003-04-27 : 22:44:35

Is there a way to control the spacing when you cast an integer to a string? The return string for this JobNumber alias example in the following SQL statement is 'Job # 3':

SELECT 'Job #' + str(JobID) AS JobNumber FROM JobTable Where JobID = 3

I know that SQL isn't a formatting language, but is there any way to remove some of these extra spaces and have the return string look something more like 'Job # 3'? I've tried to change the JobID field from "int" to "tinyint" datatype and also modified the SQL query as follows, but it didn't appear to impact the output:

SELECT 'Job #' + CAST(JobID AS VarChar(2)) AS JobNumber FROM JobTable Where JobID = 3

I'd appreciate any suggestions on this. Thanks!

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-04-27 : 22:53:02

quote:

Is there a way to control the spacing when you cast an integer to a string? The return string for this JobNumber alias example in the following SQL statement is 'Job # 3':

SELECT 'Job #' + str(JobID) AS JobNumber FROM JobTable Where JobID = 3

I know that SQL isn't a formatting language, but is there any way to remove some of these extra spaces and have the return string look something more like 'Job # 3'? I've tried to change the JobID field from "int" to "tinyint" datatype and also modified the SQL query as follows, but it didn't appear to impact the output:

SELECT 'Job #' + CAST(JobID AS VarChar(2)) AS JobNumber FROM JobTable Where JobID = 3

I'd appreciate any suggestions on this. Thanks!

It is hard to tell from your post but I assume there are 2 spaces after the # in your first example and only one in the second. Try using LTRIM to remove the leading spaces around your JobNumber

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-04-27 : 22:55:04

Hi

You can trim the string :

SELECT 'Job #' + ltrim(str(JobID)) AS JobNumber FROM JobTable Where JobID = 3

How's that ?

Damian

EDIT : Damn, sniped

Yeah, what he said!

Edited by - merkin on 04/27/2003 22:55:54

ackweb
Yak Posting Veteran

54 Posts

Posted - 2003-04-27 : 23:01:43

My return string example originally looked like 'Job #__________3', but adding LTRIM to the code (as follows) yielded the desired 'Job #3' output:

SELECT 'Job #' + LTRIM(str(JobID)) AS JobNumber FROM JobTable Where JobID = 3

Thank you for the timely assistance!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-27 : 23:14:08

bit surprised
SELECT 'Job #' + CAST(JobID AS VarChar(2)) AS JobNumber FROM JobTable Where JobID = 3

didn't work.
Can you try it again.

In fact any size varchar should work

SELECT 'Job #' + convert(varchar(10),JobID) AS JobNumber
FROM JobTable Where JobID = 3

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-04-28 : 02:20:35

Although the solution (to use LTRIM) has already been provided, I thought I'd add the following notes:

Note that the default display for str() with one parameter passed in is Right-aligned inside 10 characters with left fill spaces.

NR, when I ran the CAST version, it just left-aligned the text and right-filled with spaces.

------------------------------------------------------
The more you know, the more you know you don't know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources