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 2005 Forums
 Transact-SQL (2005)
 Using Semicolon in Select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bbowser
Starting Member

USA
43 Posts

Posted - 09/07/2011 :  17:43:31  Show Profile  Reply with Quote
I know I've done this before but for the life of me I can't remember how to get this accomplished. I need to place a ; at the character 50 spot of each field in my Select statement.

Select FName, LName, Address From tblContacts

Results Expected:
Tom ; < semicolon lands at 50 characters
Jones ; < semicolon lands at 50 characters
Address ; < semicolon lands at 50 characters

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/08/2011 :  02:35:24  Show Profile  Reply with Quote
quote:
Originally posted by bbowser


Results Expected:

Tom         ; < semicolon lands at 50 characters
Jones       ; < semicolon lands at 50 characters
Address     ; < semicolon lands at 50 characters




Select [FName] = LEFT(FName + REPLICATE(' ', 49), 49) + ';',
       ...
From tblContacts
Go to Top of Page

bbowser
Starting Member

USA
43 Posts

Posted - 09/08/2011 :  11:01:05  Show Profile  Reply with Quote
Thanks for the help Kristen but your solution didn't work on int fields for some reason. Is there another solution?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 09/08/2011 :  11:08:42  Show Profile  Visit russell's Homepage  Reply with Quote
convert the int to varchar

Select intField = LEFT(convert(varchar(75), intField) + REPLICATE(' ', 49), 49) + ';'
Go to Top of Page

kmarshba
Starting Member

USA
24 Posts

Posted - 09/08/2011 :  12:43:14  Show Profile  Visit kmarshba's Homepage  Send kmarshba an AOL message  Reply with Quote
There are missing details in your post making it difficult to appropriately answer your question:
1. How is NULL to be handled?
2. What is expected for fields with values wider than 50 characters?
3. It appears you are trying to create a fixed-width, semi-colon delimited file but I can presume that's the case as it's not stated.
4. Is a semi-colon expected to be the last character of the field regardless of length?
5. Your response to Kristen's post let's us know you want to handle at least character and integer data types but I'll presume all datatypes at this point

...so augmenting russell and Kristen's response and responding strictly to bbowser's specifications:

This will put the semi-colon at the 50th character regardless of the length, datatype, value or lack thereof (NULL):

SELECT RTRIM(STUFF(convert(varchar(max), ISNULL(FName) + REPLICATE(' ', 50), 50, 0, ';'))

Go to Top of Page

bbowser
Starting Member

USA
43 Posts

Posted - 09/08/2011 :  12:44:05  Show Profile  Reply with Quote
Thanks gang. I've got it all working now just as you suggested. I did make a minor change. Since I was needing to send my query to a text file and I used the semicolon built in as a delimiter from SQL Server Management Studio so I left off the + ';' part of the code. Thanks again for all your help.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/08/2011 :  13:21:55  Show Profile  Reply with Quote
"convert(varchar(75), intField)"

Blimey Russell ... how big are the INTs in your part of the world?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 09/08/2011 :  13:39:56  Show Profile  Visit russell's Homepage  Reply with Quote
LOL Kristen. I threw that in there when it was still a varchar
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.08 seconds. Powered By: Snitz Forums 2000