Prev: Please help with a simple Max Select query
Next: bcp header row
From: DavidC on 26 Apr 2010 18:01
I have a SELECT statement that I want to fill leading zeros in one column and
trailing blanks in another column up to xx characters. For example

SELECT CAST(EmployeeNumber AS varchar(8))
If the EmployeeNumber is 1234 I want to output 00001234

SELECT CAST(LastName AS varchar(15))
If the LastName is SMITH I want to output SMITHxxxxxxxxxx where x = space
Could this be done by casting a CHAR(15)?

Thanks.
--
David
From: Plamen Ratchev on 26 Apr 2010 18:28
DavidC wrote:

> SELECT CAST(EmployeeNumber AS varchar(8))
> If the EmployeeNumber is 1234 I want to output 00001234

SELECT RIGHT('00000000' + CAST(EmployeeNumber AS VARCHAR(8)), 8)

>
> SELECT CAST(LastName AS varchar(15))
> If the LastName is SMITH I want to output SMITHxxxxxxxxxx where x = space
> Could this be done by casting a CHAR(15)?
>

Yes, CAST(LastName AS CHAR(15)) will work.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Lutz Uhlmann on 27 Apr 2010 04:00
Am 27.04.2010 00:01, schrieb DavidC:
> I have a SELECT statement that I want to fill leading zeros in one column and
> trailing blanks in another column up to xx characters. For example
>
> SELECT CAST(EmployeeNumber AS varchar(8))
> If the EmployeeNumber is 1234 I want to output 00001234
>
> SELECT CAST(LastName AS varchar(15))
> If the LastName is SMITH I want to output SMITHxxxxxxxxxx where x = space
> Could this be done by casting a CHAR(15)?
>
> Thanks.

Try this functions ...
You should use it this way
SELECT dbo.funcPadLeft('0', 8, EmployeeNumber)
SELECT dbo.funcPadRight('x', 15, LastName )

CREATE FUNCTION dbo.funcPadLeft
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int

SET @BaseLen = LEN(ISNULL(@BaseString,''))

IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = ISNULL(@BaseString,'')
END
ELSE
BEGIN
SET @Padded = REPLICATE(@PadChar, @PadToLen - @BaseLen) +
ISNULL(@BaseString,'')
END

RETURN @Padded
END

CREATE FUNCTION dbo.funcPadRight
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int

SET @BaseLen = LEN(@BaseString)

IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END

RETURN @Padded
END
 | 
Pages: 1
Prev: Please help with a simple Max Select query
Next: bcp header row