Padd Character Function To Return Variable Type

All,
I have a UDF for SQL that will padd a varchar value to a specific width and
align left or right. I found this code online somewhere, but the problem is
that it accpets a value up to Varchar(8000) and returns a varchar(8000). I
am writing a query to upload to a mainframe and need fixed-width fields, but
each field is not the same size. I can pass in the correct width to the
function and it padds correctly, but in a DTS transformation to a text file,
it thinks all fields are 8000 characters wide.
I am asking if it is possible to return a variable size data type instead of
a fixed type.
Here is the orig function:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(8000) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
and what I want basically and which doesn't seem to work, is:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(@.Width) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
Does anyone have any ideas on this?
Thanks,
NathanWhen I need to export fixed-width fields my approach is a bit
different. I create a view that formats each column the way I need it
to a fixed length, then export from the view. Examples of formatting:
convert(char(30), ItemName) as ItemName
STR(ItemValue,9,2)
Roy|||Thank you, I rarely use CHAR since it does padd for storage in the database,
duh! That worked to replace my instances of the dbo.PaddChar in my query fo
r
all the intstances where I am just padding on the right with spaces.
However, I had to just wrap a couple of them that I am right aligning, or
padding with 0's.
Since my query only pulls back a few rows a day the performance isn't bad.
Thank you again,
Nathan
"Roy Harvey" wrote:

> When I need to export fixed-width fields my approach is a bit
> different. I create a view that formats each column the way I need it
> to a fixed length, then export from the view. Examples of formatting:
> convert(char(30), ItemName) as ItemName
> STR(ItemValue,9,2)
> Roy
>

Override SQL character

I'm trying to search for all records that contain a quotation character in the database. These records were migrated from a mainframe system.

I use the following command:
%'%

The results are:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ' order by cliLastName, cliFirstName, cliBirthName'.

/ladds/lib/getrecordset.asp, line 6

Is there a way to override the quotation character temporarily?

Thanks ...You just have to escape the character. Use this:

like '%''%'

That is, two single quotes, and not a double quote.|||Thanks ... worked like a charm!