Changing data - 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

General SQL Server Forums

New to SQL Server Programming

Changing data

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 10:28:10

I have a column which contains data about people's heights. That data is currently formatted thusly - 6 00, 5 11, etc.
I need to convert that data to be formatted this way: 6'00, 5'11", etc. What is the best way to convert that and add the ' and ''?

thanks

James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 10:35:37

STUFF(column,2,1,'''') + ''''''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 10:51:06

Use MNI
STUFF(height,2,1,'''') + ''''''

Using the above, i get incorrect syntax near "stuff", if i put "Select" before "Stuff", i get

an "unrecognized column" error in reference to the column which is called "height"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 10:55:08

it should be


Use MNI
GO
SELECT STUFF(height,2,1,'''') + '''''' FROM <your table name>

i just gave you stub, though you would fit it into query by yourself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 13:18:03

Any thoughts as to why, despite the query telling me it ran successfully, and 6,532 rows were affected, and showing me the new data as i want to see it, that, after closing and reopening management studio, it's not displaying the "'" and """"'s??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 13:33:55

oh you mean you want to change it in table itself. Then it should be an update


Use MNI
GO
UPDATE <your table name>
SET height=STUFF(height,2,1,'''') + ''''''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-10-14 : 13:47:31

i see. Ugh. However, my field is a Varchar(5). And, given that there is already a space between the feet and inches, it doesn't look like this query is removing that space, thereby giving me six charachters in that field, which it obvously won't take. How do i tell it to put the ' in place of the space that exists there, and not in addition to?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 13:51:10

it will delete that space
see example below

SELECT STUFF('6 11',2,1,'''') + ''''''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources