Fixed Width & Quotes - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

Fixed Width & Quotes

Author

Topic

sqlbeginner123
Starting Member

9 Posts

Posted - 2012-07-11 : 22:36:01

Hi All,

I am trying to create a script that will return a fixed width column of only 35 characters and I want the resutls to have quotes around them.
This is what I have so far
right(((replace((Upper(Quotename(Table_name,'"'))),'','')))+ replicate('', 33 - LEN((Table_name) )),33)

The issue is that this script for lines with more than 35 characters does not show the quotes at the end.

Results return like this:

"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Thank you for any assistance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-12 : 07:31:41

Might be simpler to cast it as fixed length CHAR - for example like this:

'"' + CAST(Table_Name AS CHAR(33)) +'"'

sqlbeginner123
Starting Member

9 Posts

Posted - 2012-07-19 : 10:14:23

Sunitaabeck.

Thank you soooo much for your help. I have been pulling my hair out trying to figure that script out. The script you suggested works perfectly the only issue I have now is that there is a space between the last character and the quote. How would I remove the extra space.

Thank you

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-19 : 10:23:54

'"' + CAST(Table_Name AS NVARCHAR(33)) +'"'

------------------------
PS - Sorry my bad english

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 21:31:26

quote:
Originally posted by sqlbeginner123

Sunitaabeck.

Thank you soooo much for your help. I have been pulling my hair out trying to figure that script out. The script you suggested works perfectly the only issue I have now is that there is a space between the last character and the quote. How would I remove the extra space.

Thank you

I thought you wanted to have the spaces padded if the length was less than 33. Or did you mean that you wanted the spaces AFTER the second double-quote. See the two examples in the code below. If it is neither, can you post sample output like I have done below?

CREATE TABLE #tmp(Table_Name VARCHAR(256));
INSERT INTO #tmp VALUES ('1234');
INSERT INTO #tmp VALUES ('1234567890123456789012345678901234567890');

-- Pads with spaces between the last character and the double-quote
-- if length is less than 33.
SELECT '"' + CAST(Table_Name AS CHAR(33)) +'"' FROM #tmp;

--"1234                             "
--"123456789012345678901234567890123"


-- Pads with spaces after the last double quote
SELECT CAST('"' + CAST(Table_Name AS VARCHAR(33)) +'"' AS CHAR(35)) FROM #tmp;
-- There are trailing spaces after the first row below.
--"1234"                             
--"123456789012345678901234567890123"

DROP TABLE #tmp;

sqlbeginner123
Starting Member

9 Posts

Posted - 2012-08-10 : 08:58:55

The sql script

'"' + CAST(Table_Name AS NVARCHAR(33)) +'"'

was exactly what I needed and worked great!!

Thank you for all your help!!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 09:52:42

You are very welcome.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources