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
 General SQL Server Forums
 New to SQL Server Programming
 select statement for replacing a character?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

racinghart
Starting Member

USA
49 Posts

Posted - 01/15/2007 :  19:48:06  Show Profile  Reply with Quote
first of all... hello, forum! im a total newbie

ive been working on this script today and cannot for the life of me get the following employee name format correct...

the data is currently "lastname~firstname" in employee table... i am trying to write a select statement that will take that and remove the "~" (tilde) and replace it with a "," (comma).

how do i get "lastname~firstname" from employee table to display in "lastname,firstname" format?

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 01/15/2007 :  19:58:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
SELECT REPLACE(YourColumnName, '~', ',')
FROM YourTableName

Tara Kizer
Go to Top of Page

racinghart
Starting Member

USA
49 Posts

Posted - 01/15/2007 :  20:18:00  Show Profile  Reply with Quote
thank you... ive come up with this now...

declare @SSN as varchar(11)
select replace(replace(name,'~',','), CHAR (10), '') as name,
convert(char(10), birthdate, 101) as bday,
Left(SSN,3)+'-'+Substring(SSN,4,2)+'-'+Substring(SSN,6,4) as ssn
from employee
where status = 'A'
order by name


the above converts the birthdays from yyyy-mm-dd to mm/dd/yyyy, ssn from 123456789 to 123-45-6789 and lastname~firstname to lastname,firstname and of course limits results to those employees that are "ACTIVE" status

thanks, Tara!
Go to Top of Page

racinghart
Starting Member

USA
49 Posts

Posted - 01/15/2007 :  20:21:35  Show Profile  Reply with Quote
my god! your way was so much easier.

declare @SSN as varchar(11)
select replace(name,'~',',') as name,
convert(char(10), birthdate, 101) as bday,
Left(SSN,3)+'-'+Substring(SSN,4,2)+'-'+Substring(SSN,6,4) as ssn
from employee
where status = 'A'
order by name
Go to Top of Page

racinghart
Starting Member

USA
49 Posts

Posted - 01/16/2007 :  12:48:45  Show Profile  Reply with Quote
tara:

here is the next tricky part... lets say the column data is "lastname~firstname" and birthday is "mm/dd/yyyy"

when i write the select statement to replace the ~ with a comma its fine but when i go to save the results as a CSV file... it comes out as lastname,firstname,mm/dd/yyyy

the assignment is to get it to render with qoutes now... "lastname","firstname","mm/dd/yyyy"

please advise
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/16/2007 :  12:51:30  Show Profile  Reply with Quote
select '"'+replace(name,'~','","')+'"' as name,
'"'+convert(char(10), birthdate, 101)+'"' as bday,

Assignment?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

racinghart
Starting Member

USA
49 Posts

Posted - 01/16/2007 :  15:08:28  Show Profile  Reply with Quote
well... its an assignment given to me... and im a newbie fresh out of college with only a semester of SQL classes and here at work i am actually a network technician and because they saw that i had some SQL experience (school) on my resume they are trying to see if i can work on a team with other sql people and since i am in the business of information, i hopped on the net and i found this forum haha i love it its very helpful. i know some stuff in sql but not like you and Tara haha one year one day, maybe. thanks for the help! that was an easy fix. i didnt think we could put '"'+ before convert or other functions. now i know.

Edited by - racinghart on 01/16/2007 15:15:58
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/16/2007 :  15:21:06  Show Profile  Reply with Quote
As long as you are combining like datatypes, it's not a problem.

If you do SELECT '"'+0+'"'

Then it won't work

Good luck, and open bol and leave it open



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



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.05 seconds. Powered By: Snitz Forums 2000