| Author |
 Topic  |
|
|
bbowser
Starting Member
USA
43 Posts |
 Posted - 09/07/2011 : 17:43:31
|
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
22191 Posts |
Posted - 09/08/2011 : 02:35:24
|
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
|
 |
|
|
bbowser
Starting Member
USA
43 Posts |
Posted - 09/08/2011 : 11:01:05
|
| Thanks for the help Kristen but your solution didn't work on int fields for some reason. Is there another solution? |
|
|
|
russell
Pyro-ma-ni-yak
USA
4964 Posts |
Posted - 09/08/2011 : 11:08:42
|
convert the int to varchar
Select intField = LEFT(convert(varchar(75), intField) + REPLICATE(' ', 49), 49) + ';' |
|
|
|
kmarshba
Starting Member
USA
24 Posts |
Posted - 09/08/2011 : 12:43:14
|
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, ';'))
|
|
|
|
bbowser
Starting Member
USA
43 Posts |
Posted - 09/08/2011 : 12:44:05
|
| 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. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/08/2011 : 13:21:55
|
"convert(varchar(75), intField)"
Blimey Russell ... how big are the INTs in your part of the world?  |
|
|
|
russell
Pyro-ma-ni-yak
USA
4964 Posts |
Posted - 09/08/2011 : 13:39:56
|
LOL Kristen. I threw that in there when it was still a varchar  |
|
|
| |
Topic |
|
Using Semicolon in Select
