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
 General SQL Server Forums
 New to SQL Server Programming
 select statement for replacing a character?
 New Topic  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
37470 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  
 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