SQL Server Forums
Profile | Register | 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
 New Topic  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
Yak Posting Veteran

Portugal
52 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000