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.
| Author |
Topic |
|
racinghart
Starting Member
49 Posts |
 Posted - 2007-01-15 : 19:48:06
|
| first of all... hello, forum! im a total newbieive 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
38200 Posts |
Posted - 2007-01-15 : 19:58:54
|
| SELECT REPLACE(YourColumnName, '~', ',')FROM YourTableNameTara Kizer |
 |
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-01-15 : 20:18:00
|
| 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 ssnfrom employeewhere status = 'A'order by namethe 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" statusthanks, Tara! |
|
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-01-15 : 20:21:35
|
| 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 ssnfrom employeewhere status = 'A'order by name |
|
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-01-16 : 12:48:45
|
| 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/yyyythe assignment is to get it to render with qoutes now... "lastname","firstname","mm/dd/yyyy"please advise |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-01-16 : 15:08:28
|
| 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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
select statement for replacing a character?