Cell Padding - 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 2008 Forums

Transact-SQL (2008)

Cell Padding

Author

Topic

Whistler76
Starting Member

6 Posts

Posted - 2010-08-07 : 08:25:04

Hi,

I've been away from SQL for some years and just starting to get back into the swing of things.

I have an (int) field at the moment that contains a 3 digit Agent ID i.e. 152

I need to pad this int field with 3 characters to make 'ABC152' within a SELECT Statement

Any help is appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-07 : 08:43:08

[code]select 'ABC' + convert(varchar(3), AgentID)[/code]

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

Whistler76
Starting Member

6 Posts

Posted - 2010-08-07 : 08:48:02

quote:
Originally posted by khtan

select 'ABC' + convert(varchar(3), AgentID)

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

Thanks, thats works a treat.

However I have just found that not all the AgentID's are 3 digit, so need to add a 0 to any 2 digit ID's ie 'ABC017'

And add 00 to any 1 digit ID's ie 'ABC005'

Can you help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-07 : 09:04:37

[code]select 'ABC' + right('000' + convert(varchar(3), AgentID), 3)[/code]

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

Whistler76
Starting Member

6 Posts

Posted - 2010-08-07 : 09:08:58

quote:
Originally posted by khtan

select 'ABC' + right('000' + convert(varchar(3), AgentID), 3)

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

KHTan,

Thanks, that worls like a charm.

Whistler76
Starting Member

6 Posts

Posted - 2010-08-07 : 10:17:34

quote:
Originally posted by Whistler76

[quote]Originally posted by khtan

select 'ABC' + right('000' + convert(varchar(3), AgentID), 3)

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

One more if you dont mind...

How can remove NULLS with the SELECT?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-07 : 10:18:21

use isnull()

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-07 : 10:32:06

or

WHERE AgentID IS NOT NULL

Whistler76
Starting Member

6 Posts

Posted - 2010-08-07 : 10:36:23

quote:
Originally posted by khtan

use isnull()

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

Sorry, where should the is NULL() go ?

SELECT

LEFT(GETDATE(),11) AS Date
'SSI+ RIGHT('000' + CONVERT(VARCHAR(3), AGENTID), 3) AS [Lead Builder]
(u.name) AS [Conf Manager]
Title,
Forename,
Surname,

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-07 : 19:46:22

which column contains NULL ? what do you want to return instead of NULL
example, return empty string column_name is null

isnull(column_name, '')

or, if the column is of numeric type

isnull(column_name, 0

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

Whistler76
Starting Member

6 Posts

Posted - 2010-08-09 : 01:31:42

quote:
Originally posted by khtan

which column contains NULL ? what do you want to return instead of NULL
example, return empty string column_name is null
isnull(column_name, '')

or, if the column is of numeric type

isnull(column_name, 0

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

Overall there are about 30 columns in the SELECT, I need to show empty strings for any NULL values in any of the columns.

Thanks for all your help with this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources