CODEWITHSUNDEEP
sql-server
Thunderhashy
Thunderhashy

Reputation: 5321

What is the best way to append spaces at the end of a char variable in SQL Server

What is the best way to append spaces at the end of a char variable in SQL Server? I have found 3 ways. Any ideas which one is better? Here I am trying to pad 2 spaces at the end of FOO

1) 

declare @var char(5)
set @var = convert(char(5),'FOO')

2)

declare @var char(5)
set @var = cast('FOO' AS char(5))

3)

declare @var char(5)
set @var = 'FOO'

what is the difference between each of them?

When I have to parse huge data which option will be quicker and efficient taking less memory?

Upvotes: 1

Views: 399

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294217

The spaces are comming from the way the variable is declared: char(5). Being a fixed length type, the value will be automatically space appended.

You should also look at SET ANSI PADDING setting. For varchar(5) type (variable length) the setting of ANSI PADDING may result in trimming existing spaces from the end of the value:

Trailing blanks in character values inserted into a varchar column are trimmed. Trailing zeros in binary values inserted into a varbinary column are trimmed.

Upvotes: 4

Nathan Wheeler
Nathan Wheeler

Reputation: 5932

Cast and convert are practically identical, the only difference being that a cast is required to switch between decimal and numeric types. Implicit conversion is fine too. The execution is identical.

The only thing to watch for is if your input is longer than your variable size, the end will be trimmed off without warning.

Upvotes: 0

codekaizen
codekaizen

Reputation: 27419

My guess is that they are all identical.

The T-SQL parser probably creates an internal expression tree from each statement, and after abstracting it, each one becomes the same tree.

Upvotes: 0

Related Questions