Using Semicolon in Select - 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 2005 Forums

Transact-SQL (2005)

Using Semicolon in Select

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2011-09-07 : 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

22859 Posts

Posted - 2011-09-08 : 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

43 Posts

Posted - 2011-09-08 : 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

5072 Posts

Posted - 2011-09-08 : 11:08:42

convert the int to varchar

Select intField = LEFT(convert(varchar(75), intField) + REPLICATE(' ', 49), 49) + ';'

kmarshba
Starting Member

24 Posts

Posted - 2011-09-08 : 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

43 Posts

Posted - 2011-09-08 : 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

22859 Posts

Posted - 2011-09-08 : 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

5072 Posts

Posted - 2011-09-08 : 13:39:56

LOL Kristen. I threw that in there when it was still a varchar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources