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 |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-02-23 : 09:46:15
|
| I have a column representing persons 'heights'. Currently, that data looks one of two ways. For example, it can be 5 07 or 05 07. in the first case, i need it to look like 5'07" and in the second case i need to look the same, dropping the 0 from in front of the 5. is this a "STUFF" issue, and how would it look?thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-23 : 09:52:03
|
| STUFF(STUFF(CASE WHEN Field LIKE '0%' THEN SUBSTRING(Field,2,LEN(Field)-1) ELSE Field END,2,0,''''),LEN(Field),0,'"')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-23 : 09:59:48
|
| yikes. where do i reference the table name to direct that? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-23 : 10:04:20
|
| [code]SELECT STUFF(STUFF(CASE WHEN Field LIKE '0%' THEN SUBSTRING(Field,2,LEN(Field)-1) ELSE Field END,2,0,''''),LEN(Field),0,'"') FROM table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-02-23 : 10:14:25
|
| Are you going to store heights as strings? Is it just for information, or do you plan on querying against them (e.g. everyone between 5'2" and 5'11") Strings will make the latter difficult, better to convert them to int or tinyint and let the presentation convert it to feet and inches. |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-23 : 10:14:55
|
| Ack! That turns my heights into 5' "08 as opposed to 5'08" |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-02-23 : 10:21:55
|
| disregard and thank you. fixed it. |
|
|
|
|
|
|
|
|
quick fix? 'stuff'?