Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Fixed Width & Quotes
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlbeginner123
Starting Member

9 Posts

Posted - 07/11/2012 :  22:36:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/12/2012 :  07:31:41  Show Profile  Reply with Quote
Might be simpler to cast it as fixed length CHAR - for example like this:
'"' + CAST(Table_Name AS CHAR(33)) +'"'
Go to Top of Page

sqlbeginner123
Starting Member

9 Posts

Posted - 07/19/2012 :  10:14:23  Show Profile  Reply with Quote
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
Go to Top of Page

jleitao
Posting Yak Master

Portugal
100 Posts

Posted - 07/19/2012 :  10:23:54  Show Profile  Reply with Quote
'"' + CAST(Table_Name AS NVARCHAR(33)) +'"'


------------------------
PS - Sorry my bad english
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/19/2012 :  21:31:26  Show Profile  Reply with Quote
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;
Go to Top of Page

sqlbeginner123
Starting Member

9 Posts

Posted - 08/10/2012 :  08:58:55  Show Profile  Reply with Quote
The sql script

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

was exactly what I needed and worked great!!

Thank you for all your help!!!!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/10/2012 :  09:52:42  Show Profile  Reply with Quote
You are very welcome.)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000