Concatenating Varchar Columns, Padding Added?

Oct 19, 1999

I have a table on two different servers, the only difference that I can see is that on server A columns first (varchar 32) and last (varchar 32) have ANSI_PADDING set ON and on server B those columns are OFF. No idea why this is true: I didn't specify that the table be set up this way and they both followed similar creation/upgrade paths.
I execute "select last+first from <table>" on server A and the result looks like:
<last1> <first1>

<last2> <first2>

...
On server B I get
<last1><first1>

<last2><first2>

Now the docs say ANSI_PADDING has nothing to do with this behavior; in fact if I copy the data on server B to 2 new columns with ANSI_PADDING ON I get the same results. But that's the *only* thing that was different in syscolumns. What is causing the different output behaviors on these two servers? Thanks.

View 1 Replies


ADVERTISEMENT

Varchar Padding

Jun 12, 2000

upgraded to 7.0 and all was fine until...

we run a job that pull selected fields from a table and writes them to a dos text file.
(tables get loaded via bcp)

Pre-7.0, all varchar data was correctly output without any padding.

NOW, all varchar fields are padded with spaces on output.

tried toggling the Set ansi_padding switch before creating table def, didn't work.

HELP! (please?)

fw

View 1 Replies View Related

Char Vs Varchar &#043; Padding Issue

Feb 20, 2008

I have upsized some tables from ms access (using the wizard) which has created many nvarchar fields. I know the system doesn't need unicode characters so I straight away changed them to varchar and the system is all working but am now deciding on which ones to change to char as I understand you get a performance gain. I am trying to do this without changing much ms access front end code so don't want to deal with rtrim I am thinking change the ones I can guarantee the length of. These happen to be the primary keys.

My questions are is this the correct way of doing things, am I correct in assuming I would have to rtrim alot in program code if I changed all to char.

Finally and. most importantly what about a single character field which could be null can I make this a char(1) and it will still return null if empty as it would as a varchar(1). I suppose this question is how does the padding work is it the character followed by a number of nulls (ascii 0 I think).

Sorry for the multiple questions but I think they are all related

View 2 Replies View Related

Converting Timestamp To Varchar Or Concatenating It With A String

Sep 20, 2007

Hello,

I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.

--code within cursor

SELECT '
DECLARE @Result INT
EXEC @Result = DELETE_DOCUMENT
@DocumentID = ' + STR(DocumentID) + ',
@TimeStamp =' + CAST([Timestamp] as varchar) + ',

-- CHECK RESULT AND STATUS
-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL
FROM Document



The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values

T
T€‘
T­
xnĂž
T!
T"
T#
T$
T%
T&
T'
T(
T)
T*
T+
T,

instead of


0x0000000013540F1C
0x0000000013540F1E
0x0000000013540F1F
0x0000000013786EDE
0x0000000013540F21
0x0000000013540F22
0x0000000013540F23
0x0000000013540F24
0x0000000013540F25
0x0000000013540F26
0x0000000013540F27
0x0000000013540F28
0x0000000013540F29
0x0000000013540F2A
0x0000000013540F2B
0x0000000013540F2C


which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.

Thanks
Sam

View 3 Replies View Related

Concatenating Columns

Dec 13, 2007

Hi how would i concatenate columns from my database into one column, these are the columns im trying to concatenate;
SELECT  Companies.companyName, Companies.companyAddressLine1 , Companies.companyAddressLine2 as Company, Companies.companyAddressLine3, Companies.companyTown, Companies.companyPostcode, AS COMPANY
Thank you

View 7 Replies View Related

Concatenating Columns

Mar 3, 2004

I have two columns of type integer that I want to concatenate. I also want to put a "/ " in between the columns.

I have tried doing it in the same way as I would for varchar columns but that doesn't seem to work for integers.

Thanks

Rupal

View 2 Replies View Related

Concatenating Columns Of Different Data Types

Aug 4, 2006

We have a table in DB2, which has a column1 (Data type: - decimal (10, 0)) and column2 (Data type: - varchar (70)), I need to concatenate these two columns into one while displaying output

Procedure I followed:--

Tried to convert column1 to varchar using cast function so that I could concatenate both columns. However I ended up in getting the following error message

DB2 SQL error: SQLCODE: -461, SQLSTATE: 42846, SQLERRMC: SYSIBM.DECIMAL; SYSIBM.VARCHAR

Message: A value with data type "SYSIBM.DECIMAL" cannot be CAST to type "SYSIBM.VARCHAR".

Please do let me know where I went wrong

Thanks
Pratibha Surya

View 2 Replies View Related

Dynamically Concatenating Multiple Columns In Sequence?

Oct 16, 2014

I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths.

eg..

Input 1:

Table A: (below are the fields and their respective values, not all fields will have values)

-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH
---------

Table BSadIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)

---------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN--> 10|10|4|10

Expected Result:

---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234

Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif

then after concat the value should be like below:

12345678904355325363a234 88390234

Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..

KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH

I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...

I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..

View 2 Replies View Related

Can't See Added Columns In ASPNETDB.mdf

Sep 27, 2006

In developing a VWDE project I added 2 columns to the User table in the ASPNETDB.mdf database.  I can see the columns in the Data Definition and I can see the values I added when I Show Table Data but I cannot access them with a SQLDataSource control?   The SQLDataSource shows all of the columns in that table except the ones I added.  Any suggestions? Thanks.

View 2 Replies View Related

SQL Server 2008 :: Concatenating Multiple Rows (Certain Columns)

Jan 29, 2015

Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.

current:
IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015

Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015

View 4 Replies View Related

Can Columns Be Added To An Error Output?

Aug 7, 2006

When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?

I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.

IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;

// Add extra column here, e.g. ErrorMessage

View 7 Replies View Related

SQL Server 2014 :: Dynamically Concatenating Multiple Columns In A Sequence?

Oct 16, 2014

I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths. eg..

Input 1:

Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH

Table BIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)

KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR--> 1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN10|10|4|10

Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234

Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif then after concat the value should be like below:

12345678904355325363a234 88390234

Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..

KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH

I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...

I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..

View 2 Replies View Related

How Can I Set Constant Padding Between The Columns Of The Column Chart(stacked Column Sub-type)?

Aug 2, 2006

Hi All,

I am working on a column chart type (stacked column sub-type) report.

Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).

Thanks a lot in advance!

Danny





View 2 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Convert Columns In Table To Int From Varchar

Nov 11, 2011

I have a table that imported as varchar. Most of these columns need to be in a numerical format. How can I convert a table with columns named column0 (needs to be int),column1 (stays varchar), column2(needs to be int), and column 3(needs to be int)?

View 4 Replies View Related

Transact SQL :: Parsing Varchar Columns

Sep 22, 2015

I'm attempting to use T-SQL to strictly parse/pull Names from a string field like such: CN=John Doe,OU=xyz,DC=ituy,DC=qwer,DC=org...I would like the ultimate result to be JUST the full name John Doe (pretty much everything after the first = sign and before the first comma. I'm attempting combinations of REPLACE, STUFF, PATINDEX and SUBSTRING, but to no avail.

View 5 Replies View Related

Transform One Varchar Column Into Many Bit Columns

May 19, 2007

Hi all,
I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example
Admit = Admit
Accept = Admit then Accept
Withdraw Accept = Admit, Accept, then Withdraw
Decline = Admit then Decline
As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits:
Admit
Accept
Withdraw
That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns?
Thanks for any help or suggestions you have.

View 13 Replies View Related

Default Empty String Value For Varchar Columns

Jun 4, 2008

Hi guys,
Is there a way to declare a default value of empty string '' for a varchar table column?
Thanks,Kevin

View 4 Replies View Related

Method For Compressing Varchar/nvarchar Columns?

Jul 20, 2005

I have an application with highly compressable strings (gzip encodingusually does somewhere between 20-50X reduction.) My base 350MBdatabase is mostly made up of these slowly (or even static) strings. Iwould like to compress these so that my disk I/O and memory footprintis greatly reduced.Some databases have the ability to provide a compressedtable, compressed column, or provide a user defined function tocompress an indvidual Field with a user defined function[ala. COMPRESS() and DECOMPRESS() ].I could right a UDF with an extended prodcedure if I need to but I'mwondering if there are any other known methods to do this in MS SQLServer 2000 today?--Frederick Staatsfrederick dot w dot staats at intel dot com (I hate junk mail :-)

View 6 Replies View Related

Tansact SQL - Linefeed Characters In Varchar Columns

May 22, 2008



This is obviously a radical idea but some actually DO want to store linefeeds in varchar columns.

In MySQL I can escape difficult characters for example

INSERT INTO sometable(address) VALUES("23 SomeRoad
SomeTown
SomeCounty");

Does anyone know how to do this in Transact SQL?

View 7 Replies View Related

SQL 2012 :: Query Returns 13864 On A Varchar Columns

Mar 24, 2015

We have a customer that is running SQL2012 and we are seeing a weird result on a query when we run it on their db. It is based off of a table that has about 30 columns but in this case we only care about 2 of them.

[Number] [varchar](15) NOT NULL
[Person_ID] [varchar](12) NULL

Here is the query we are doing:
Select Number,Person_ID From TableName where LP='ABC123'

The result I get back is the following:
Number:1
Person_ID:13864

The Person_ID should be a result of another table that created that Person_ID but it doesn't exist in that table. So we do not know where that 13864 is coming from. When we open that record through our application it shows Nothing for the Person_ID in that field.

When we do this query on our copy we get back
Number:1
Person_ID:

Which is exactly what we should see as the result.

Could there be a sql server setting that is set on their server that could possibly be given us back 13864 for a NULL value?

View 2 Replies View Related

How To Reclaim Space In Columns Changed From Nvarchar To Varchar

Jul 23, 2005

Hi,This is probably an easy question for someone so any help would beappreciated.I have changed the columns in a table that where nvarchar to the samesize of type varchar so halve the space needed for them.I have done this a) becuase this is never going to be an internationalapplication, b) we are running out of space and c) there are 100million rows.I have done this with the alter table statement which seems to work butthe space used in the database hasn't altered.I'm presuming that the way the records are structured within the tablethere is just now more space free inbetween each page???Is there a way or re-shrinking just an individual table and free upsome of the space in there or am i missing the point somewhere?Thanks in advance,Ian

View 4 Replies View Related

DecryptByPassPhrase Not Decrypting Varchar Columns After Copying A Database

Jan 18, 2007

I have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:

DECLARE @pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @pss OUTPUT

SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator

Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.

View 5 Replies View Related

SQL Server 2012 :: Explicit Casting Varchar And Nvarchar Columns?

Feb 26, 2014

I know that if I have an nvarchar column I can use an equality like = N'supersqlstring' so it doesn't implicit cast as a varchar, like if I were to do ='supersqlstring'. And then I'll be a big SQL hero and all my stored procedures will run before a millisecond can whisper.

But if I'm comparing an nvarchar column to a varchar column, is it better to cast the varchar 'up' to an nvarchar or cast the nvarchar 'down' to a varchar?

For instance:

cast(a.varchar as nvarchar(100)) = an.nvarchar

or

cast(an.nvarchar as varchar(100)) = a.varchar

Leaving aside non-matching, like (at least I don't think) that SQL considers the varchar n to be equal to the nvarchar Ĺ„, what's the best way to handle this?

Pretend for a moment that each column contains a mixed letter and number ID with no accented or wiggly-squiggly Unicode characters; it's just designs clashing.

Is there a performance hitch doing it one way or another? Should I use COLLATE? Should one of the columns be altered?

View 8 Replies View Related

T-SQL (SS2K8) :: How To Compare Data (join) Based On Two Varchar Columns

Mar 15, 2014

-- My first Data

create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')

-- My Second Data

create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')

--Third Second Data

create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/

i have doubt on two things

1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second

2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found

--i tried below to solve first doubt, it is working but want to know any other wasys to do it

select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')

--Expected results wull be

create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')

[code]....

View 1 Replies View Related

Script To Search For A String In All Varchar Columns In All Tables In A Database?

Sep 14, 2005

I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.

View 1 Replies View Related

SQL Server 2008 :: Split Varchar Variable To Multiple Rows And Columns Based On Two Delimiter

Aug 5, 2015

declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'

---------------------

Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)

select * from #tmp

I want to convert to @var to same like #tmp table ..

"@" - delimiter goes to rows
"~" - delimiter goes to columns

View 6 Replies View Related

Padding...

Jul 31, 2000

Hi,

I'm using SQL Server 7.0. I am importing data from an EXCEL spreadsheet. I need to pad my agent_id column from the spreadsheet with zero's. Here are the values from the spreadsheet:

agent_id
--------
123
4567
112233
9

This is what I need to see in my database column:

agent_id (char(6))
--------
000123
004567
112233
000009

Is there a setting on the column in SQL Server 7.0 that I can set to pad the column with zero's?

Thanks in advance,
Darrin

View 1 Replies View Related

Removing Padding From Var

Feb 7, 2008

I have a search box to look up using a number as an identifier. Currently my query is where x = @enteredNumber, but some place where a user might get the number it is padded with zeros. We can have them just see the number and retype it.
 EG: 000000123  would be entered as 123.
 But, I would like to let them copy and paste, but then strip the zeros (left padding only).
EG:
@num = stripPadding(@enteredNumber)
IE:
@num = stripPadding(000000123)
@num = 123
 

View 2 Replies View Related

ANSI PADDING (pt.2)

Jun 13, 2000

ok people, this is getting seriously frustrating! Please help!

As mentioned in a previous post, one of my batch jobs is printing fields with padding added, even when
the table column is defined as varchar.

I've been to the knowledge base, read the article on ansi padding, ran the test scripts.
But when I ran the select to display the columns,
THE OUTPUT FOR BOTH TABLES WAS IDENTICAL!!!
Apparently the SET ANSI_PADDING ON/OFF option had no effect!

What am i doing wrong? What is missing? Do i have to run the Set Ansi Padding option in the Master DB
context? Have I unknowingly overridden the option somewhere else? Must I brush up on my COBOL
for a career change?

HELP!

fjw

View 2 Replies View Related

Padding In SQL 2000

Apr 20, 2007

Hi All,
Is there equivalent of LPAD in SQL 2000 similar to that in Oracle?
I am trying to work out for below piece of query

TO_CAHR(LPAD(columnname,20,'0'))

vishu
Bangalore

View 6 Replies View Related

Right Padding Equivalent

Jun 28, 2006

Hi everyone,Please excuse me if this has been asked before or sounds a bit dim.This is a question asked on another forum but the solutions beingoffered are focussing on programming rather than letting the DB serverdo the work, which I'm not sure is the most efficient solution.However, my confession is I dont use SQL server so can't help themdirectly with the syntax. Hopefully you can help me help them and learna little about SQL Server in the process.Trying to right pad a first name field so the padded string is a totalof 30 chars. It will be output concatenated with the last name field,and each field separated with a "|". So that when output it readssomething like:fname | mylastnameSyntax given was:select id,substring((last_name+','+rtrim(' '+isnull(level,'))+''+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_namefrom studentIssue: It appears this is padding correctly but the spaces are notrendering in the browser. (I have no way to check this as I don't usesqlserver. However, I can understand that multiple spaces are not goingto render in the client browser, if indeed the query is padding withspaces.Question: Instead of using space(), can replicate() be used and aunicode space representation rather than an actual space be used? Or,is there a better way that will ensurethe padding shows in browser?I guess a fixed width font would also need to be used otherwise the30-char blocks could wind up being different widths, which would defeatthe purpose.If there is something I've missed, or you have any suggestions, I'mkeen to learn.TYhanks in advance,Lossed

View 13 Replies View Related

Borderstyle And Padding

Apr 25, 2007

I need a table similar to Fig-1 on my report. The subtotal and total lines need top padding and solid border at top and bottom. I€™m currently using bottom padding of 20pt on the sub total row and setting the border style property of the sub total and total rows to solid at top and bottom. But the problem is that the sub total rows are being padded first and then the border at the bottom is being set to solid. As a result, my table looks something similar to figure 2, which is not acceptable to my customer. I also tried by setting the top padding of the lines below subtotals, but it doesn't give me what I need. Any way I can set the border before padding? Appreciate your help.

Figure-1





A

10


B

20


C

30


Sub Total

60







D

20


E

10


F

50


Sub Total

80







TOTAL

140

Figure-2





A

10


B

20


C

30


Sub Total

60







D

20


E

10


F

50


Sub Total

80







TOTAL

140

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved