SQL学习之数据列去空格函数

时间:2023-12-27 22:36:07

1、LTRIM()---去掉列值左边的空格  如下代码:

select * from dbo.course where tno='t003' and cno='c0013'

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAOwAAAAyCAIAAADQhaAcAAAF+klEQVR4nO1c327aPBQ/D9UiL+9STYUqD1KpUj+gEk8xqVq3QSflGXpRqRKlvUH6tm5dxy5QKWuBhiB24fyxHTskhBOS1D9Zk3tizjk+/nFy4uDBx09fl5gYj8eo+hMhV86UBtlHVbAImsQaKVF+Ej89PaHqT4RcOVMaZB9VwaImsUZaaBJnilw5UxqUn8Sj0QhVfyLkypnSQIxqv2UYrX6GFtFJ/Pj4iKo/EXLlTGkgRhWfxIJF+Pjp63zuTGc2RnOcxe/Bn5D+8xq4qHXs6c0JISeNGiOZ2dObE+IKDtrbdqbtjwjGHNQIAABpnjfczi3v9rvGDUpI89ZCUb2lAQFy0sNZWWpRJLE9dybTV4zmOItfD795/bd1AtU27XeqQOrtJgFP0msS0rxmxlw3CNQ6W3WGUUIlvSYBUu+9TtiO6zap9xSfLWmTRNWfew9lZanF7Ei8WCzufz3IZyiV+CwRJFt0xlsMF6x7QocdBgDgEbrULSqqOCtLLYZIbM9fJjOM5iwWP+8fOP3XTUKaXXYYK6H9sGSLzkw6VSD1a5l7Que6SaD2BSeSuW1RUcVZWWpxqySe3NQJVNu0793BxYkFY7p1AtXOVp3pVH1qtmsQQWJW25shdDISb2Jl5SR+tefPL1OM5jiLux/3If3tqnfHrX6ZPncbFdLo0kt+v9uouPdk79L2nOnWmTKBNLqsk0In0Eb+66KENG9NFlUahNpnnJWlFkUSz17tv88TjOY4i+93P/H0F9eZ0rTso0othkg8s8d/XzCa4zjfvv/A019cZ0rTso8qtSiSeDp7fRo/Y7S54/z/7Q5Pf3GdKU3LPqrUokjiGw2NoiH8xg4Rtm2j6k+EXDlTGmQfVdu2RRKPkAEAufpXo+hY8nBJvETDaDQaDMc5aagzfbPIOKrbITEAbJ2+tAEA3kzfLN4EibfOXZ2JUfEmSKwzcbmRcxJbJphWapPxSfbhvfd29/2ZSjIYjgfDsz3Y/xD61N7pJjJxv2V4NoPJW6YnY37vHf3r77AeRhLIpcKlZQIf+n7LANNKoIFD4L53LYYkEHCzli3xyqAmciCac/FJ7AdmAySGmJn48nhn9/hiOB4Mrw53K4eXMsnw6nCXOuaR+HQf3DEcs9fNxJbpLxjlzVJgFD9AubgyPdLxgtD7Uy5OooEVsF8jl/fumLA9vxc3hclJzLoRx1wQsBV2852JT/fZBLx3KpPIMrHXfKKnyMQSloREflQiSByHbXKhp52TK6gdrSHCL2aI2w1LYp8yWk3iOObU81lpziWx4oTSeS316SB1Jj7b8+4nlJ0XR5Wdoyt6lfbDkigSXx7vbCAT91sGf++UhNQyqSQq2iE9qvHKPMpc8LtrZWLBK9OSZElJ3mSqlGgyR93JQ189pTnRxwTmXBIrDnd0qnDQTvfLf0Umvjrc9TPr2R5UDi9TkfjiqAKbqomD1VMkv+jyQKWHFbC1n1TIry5H7CQaJA7FZ1Vwf19Bq5WZOD6J6VTWrIlRSSzJxEGxyxExVSbmvhhrZ2IGdBX523q/LyVXDD2q8WLZEGaxgg5xNPCjpMX86tS4aqIbJHH0FFTmPBLLD3cwhxrWbfJMLCNx6ppYIPramdgHrdb41QDDNI3VNbFET7xigKsQ+y3DaFmR/FqhIRgCijExitS0JMY2lwWJZTWxpJyItzshkvjiqOIRfQOZmEsCfgZldydUCS2OnuSPZf2WYRiSB7z4GpaC/+JHlNsFzBSYnRYZNrI7IdlCiW3OJbHihFK7CrXP6Y6vqHcnxAe7Qep94ug0HDMTM7ujTCTZfeKWKS9Gw8wRLojFK8h2zUIVhbAznVQDv98LwHwxee8iJMkf7PxPh/acEcy5JMY7XlLKN3aWlXLjsVTIyxs7vOMl+rcTpUdeSIx3vKSUmViDRV5IjHeMRGfi0iMvJMY7SaIzcemRFxJjA3JwKgn02aRSIExr9P+fWEMDG5rEGoWHJrFG4aFJrFF4aBJrFB6axBqFhyaxRuGhSaxReGgSaxQemsQahYcmsUbhoUmsUXj8A4v3z7H1YU1cAAAAAElFTkSuQmCC" alt="" />

我们发现这条行数据的cname列值最左侧存在一个空格,为了数据的准确性,我们现在需要去掉空格,下面是解决代码:

select LTRIM(cname),cno,tno from dbo.course where tno='t003' and cno='c0013'

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAPQAAAAvCAIAAABbtHBFAAAGfklEQVR4nO1c3W7aShCeh2qQ5XeJqtgRDxIpUgNE4iavUClqTgKpxDPkolJ0yM8NUtu0adpzUYUA4ccYcS4Wm/Xu7HoNrGOb/TSqlvF4Zjz7eVg7bOGodjLPIY5qJ9cZRr/fP6qddA3eFJBfchsxIpe8ktvAIBYRcn/657MRI4URltxa76Rer6fV/7YhO/VMPxOViIbcOUZ26mnIPX95edHqf9uQnXqmn4lKxOKQGwBiNQWDIbccWSB3qwzl1nwOYixyFejDo4xfAbkX4QoAQ245UiV3t9vllZ26zXNN0nRFnOb1wpuhKPRG6/kmYDPp1G273kkzIoZUyf38/MzpcKKtSW70dEqJ3lD5A1bPtwGbiX5yq1w7S+7p1B+NPR3i+7Pff/5j/Tf3wb3kjQFA5IfuxLQlM2A8MB8bLrhNLZeZmuD1HF+6QXHcpje6O7as46pLacbe6O7YWij2G1oyua+SANbxrZ4ESMTE5Pam/nA00SG+P/v19Jvxf1O1rOr9cDSRLLgJiH04CMfoIcaAPxTGza9g9byvWOA0yLjpgFVp1CwINLc1y6rdUDY3VQvcppZMFrEmw1stCZCIGSL3bDZ7/PUkIjctDGtFehVy80pJ3HwJUs+QUqiGjHmN1kz0JEAiJie3N30djnWIP5v9fHxi/LcrllW5YywBIPwXFdogNKPtJd8Akrj5EqSeNzXLqrVpM1pDxrxGayZ6EiARs07u14YLTpPWoGTlhTcLuS65N0LlhQNOQxft0hGsnncVK7yuYFnCMmlp065YTPE3lomE3JtIYEVyT7zp4HWkQ3x/9vDjkfPfcMA9Dz4CwCBuzH9MbnN7ZC2D5lTE9VzAuRgN2tWSVW2TQ+G4XS0Ri/DQ5jMhabjnehIgEROTezzx+oOhDvH92feHn7z/f4+svfNhfzAEAFpPfxSNRfbkI4r+YNg/d8BpaLrM1ERUz23IhERMTu6x1+u/6hDf9799/4H5v9gDBwD4U2hShhqJGaNELUm4Mz3XmKaI61n8TEjExOQejScvvYEOmfr+128P+vxvm2SnnulnQiImJvedgUFOkJjcU53wPE+r/21DduqZfiae5yUm99tuVzbIKQAg5X9jmY2TW+W01aDV+RYiO/Xsdrt//vZSE1D7pb4hd46RnXoCQJrkNp27+MhOPU3nztBkFAPZqWfeO/cGtq/IcurU7eBvMcswrXKgo376Lv8hPO+H0iz1qHLeKkP0Ijt1G8qtBB4iWKYfHFPQLBWRq05aTw4qyQRqZAbkc6/SuT++D8K9P4vV7J6m1LnDadRH7lY5nEjCpznDtKiBcNIxP6g9oww+4uokHmgFfXst7oeFDR8vHCVoIgnIrRSam+hlOWOyArRzf/nwbufDFTu+PtgpHXzBNKd7sNCc7cLex4J0boQ9nCqMLyG3CgtxZeA9ohdQXu5BkhdlshjymiQbtBKQWyW07EJi0sI7N03u0z26Pe+eYprluQHdN965BTvNLt21dyWJJ6NTt6PfwUgxW+WwvwjrzPkR2Qv7LnWA7mjJOzeTVbkVMSFjXkOvdmJJLu1EkeWEUujluRy5qa8hFFjnvj7YAQAgzfjqsPTu8JocImNeE7krNHVuwWacpgP7jfW2bEhzCmdV0CzlywyRH1pBTzeqDMxZSif0gCTEXZOQYcuVQByhhOSmT1y4W4fc5EJXWXNTnVud3FeHJY1r7jciN1VbpvbzTqcT10dFfkT27PKDZzfWwhU9RK3Qh4U4hgnD0sDriZ22bueOu9Ni19zJOvff64MdGb/X6Nz4ZpymA+7Fels21HIixY1yC+xy2Y5fcyN+1BYVkQnt1G273pLyLsbD0gQENnELX6ULVSf32mvumGQ2vebm6b6pzp06uSNtIey49NsSUQNU8ZP8cbBTt20bebBU9zBn8mdPEb6yoC6BevMjgPqyRO1tCXshyAsfATbytuTqsBTQXVvnFuw0i+wHW00kOVFvd6NNJHy4qpfxxS7PKOYAuzjmFqH85DEtdwUP0ffV4RNAojffG3ygVAwdqJH33LHfIRgLz3YBIHg0TPSeW9K21+rc+vYLrfkXtVarAP9L1CZh/kIZkxVCbm37hbIzGcVAduqZm9+W6NsvlJ3JKAayU8/cdG59+4KyMxnFQHbqmZvOrW9rUHYmoxjITj1z07kNDFYApLvTbBVyJ71lDQwyC0Nug8LCkNugsDDkNigsDLkNCov/ASqSJRNjqdN3AAAAAElFTkSuQmCC" alt="" />

2、RTRIM()---去掉列值右边的空格 ---作用和LTRIM()一样,这里不做介绍

3、replace(计算字段,' ','')---去掉计算字段列值中所有的空格,如下代码:

select cname,cno,tno from dbo.course where tno='t003' and cno='c0013'

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAPQAAAAwCAIAAACpNMALAAAGEUlEQVR4nO1c3W7iOhD2QxXk7bugCqj6IEiVeoBKfQoktJxd6JH6DL1AQuKnN5XObne73Z4LVMq2/IQgzoXzM4mdxEmYkCb+NKrcqTMzGX9MBoNLdrvd57//2WFiPp+j2s8t0pPY5COR8Uh2+OR+fX1FtZ9bpCexyUci41GR+wMjPYlV5FbYM9KT2PySezabodrPLdKTWHck91fHx1f3SXoUIQlyv7y8oNrPLdKTWHck+OSWuXeD3JuNvlxpGKLr29/P/3H2r6vEQLWnLSeXlF42qkCz0rrWDHvOaZUSQghtXjeMwd1ypS0nl9SY+KkxQbmLFErkxIJ0nXZRIrljq0Po5RgnAOZRltzaRl8s1xii69tfT7+d9u/qlFS6bNyrEFrvNikxNeMmpc0RNMI04yYltD5eL+CANkfLuzql9bHHtdmVqIm154walFR7KJFYCzFGCYB5PDy5t9vt468n8Z0LNc68GGDktvRwAKcRQohJ9KxLxMQKU40XCU4AzKM0ubXN+2KFIfp2+/PxyWF/1KS0OYTToMYY9yqE1kdAY81xDUZNSqpfcYJPs0RMrCDVmJHgBMA8ppLci0mdkkqXjc2np4DcJmW7VeJDbmgtT0SPmlh7zrBOSaWHEokPufcRQDhyr7XN2/sSQ3R9+/DjkbPfrZhtROXr8m3YKNLGkP3JHA/roN2gjaE1hx/Y1uhfQ5S7SKFETuzbsFG0sooVCQuj+gUnAOZRltyrtfbnbYEhur79/vATz35uJT2JTT4S5lGa3Ctt/ucdQ3Rd//b9B5793Ep6Ept8JMyjLLmXq/Xr/A1DNrr+77cHPPu5lfQkNvlImEdZck8UFD4aZMm9wYSmaaj2c4v0JDb5SDRNkyX3DBmEEPUT6Wc+EchsB7llZkfDbDZ7ns6V7F1QVy0UEo4kXeQmhBycCtkTQgjeqoVCrsl9cB5kUlTl9oeq3B9YVOX2RyC5b87I2U3sUIKXqn9xZH5uXGqbynbZ1BVrfTCzcHErbwdobL1QOe2UoHfj2nIrhAWHtE7Mv510pDWdkm0S3LVIQlHq5sy0aq4nr+FX3JrjTwKZSGQC2KO7nS+576+OZRwFQ6Jyd0rWQjI+Gcwut4QTPMktsiOc71Kav96eF4/OB9Y049cwFuC1Jl8HtYL5ejDmDGqFYq0v0kw7Jfuu91e57aMx5ojX8Ct+c0aMOQE1Tsw2eB5HJoCY7jiko3IL2GMtNqjijCs+5JZhoVhpUsqh5ykoYUEkxovEuoXpvHVCSu25QOP/XHJKiMoNltEY8hq/FQ84NxZM7hABRHXHwSa3x0mz62rsw0gSlXtQKxBYMkVr3Ckxjd/yc3YkyS2ooEAZqXK7oiq1HY8FNuY1sNtx3wgn3pXb/cDna6i4qgqZxmaELqXmc8BVpoMDiOZOAJvcHodxehVy2o13UkNut2RQK4BGk+eKf5vhZWfKN8emEV4JSQZblJAW+LJNuJ7Hk9ztMrFfZoI+XqJyQ2oYT/o45GYkjdIEA7vyAUR3xyEhcgdVbiBsdZ3twW0/qI562ZGqu04aGX8SlXBJCyJmP0uS23mtf/EWV27RIz1u5Q6qpXskd3R3HAC5xYdxwImYqBJyn5s1r05ukWLppBjccwvsyDUVoPc1OpM2mBDagoDZrjmePXcYcgd3uhbi9twBXfC+e+5I7jgkRG7/yg02FkDFhbsl/Ysjid0SsZ3wbwdvz4tHBUCsaG8oHbs9rks8d0vALYCdHw/x6LkFbYncbombacBQ3MotE0BcdxxscnucNOtWSPVLvGNIMpXb3u6FhID73OdlcbPrrJcCO9yGtGh3j+tMXDvrYS04IjEvCbnzHfiG0nePy4BFkZj73P7/Y8cjEna1YS3UPnckd27Y5MY7JrSvTyhbbfdzP+fiUbkPgLR/Qol3TEh9twRJ1HdL/GGTG++YkPpuCZKoyu0Pm9x4x4FU5UYSVbn9AT+hxIKq3EiiKrc/EjpmNjv0Ways/swnJF8DSfx/bgWFg0CRWyGzUORWyCwUuRUyC0VuhcxCkVshs1DkVsgsFLkVMgtFboXMQpFbIbNQ5FbILP4HuXsuALphjuYAAAAASUVORK5CYII=" alt="" />

现在有个需求,需要去掉cname字段列值中的所有空格,下面解决代码:

select replace(cname,' ',''),cno,tno from dbo.course where tno='t003' and cno='c0013'

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAOYAAAArCAIAAADjfmOGAAAGWklEQVR4nO1czU7jSBCuhyKRxbtEKDbiQSIhQRIkLnvnhBRtFhJW4hk4IKENPxeknWGGYdhDhGNCfhxH7KFtp91/bsfp2A5dKkV2ubqq3P3lc9NxA8Ph8OTkxLbtw+Yfn1q05F6ACdk///pbq9Z8KheySr8ojuMojb/Zkp/eW38ljuNoyBZP8tN7Xwiyg8FAafzNlvz03vorGQwGGwhZAIi1FFo0ZPMD2cs92Lv8/AS+ID+ePbxKxOVA1k9XONGQzQCytm3TxsfjbRpBAoLkIZW2cyFeTNAyey8TISt5PN7ePn5UnDEbyL69vVE2NnxSQpbZHDMyvyZ5F1bvZSNkJeoh+/b2xoXsbOaNJ64K9bz579f/yPjdXbAuaGcA4MXBWRP3JA6ICMRpxwKrq+Q219p7kwsr6Aqr647vjwzjqGFhlok7vj8yfMNuR0klDw2UwDi6U1MAysiFrDvzRuOpCvW8+a+X30T824ZhNB5G46lgIosE+YcH4THzEuFAXwrzFkVZvfdQN8DsoOOuCUa90zQgsNw1DaN5i/ncNgywukoq8XNNR3dKCkAZM4DsfD5//vXCgyyuBBZ5dhnI0kZB3jwro/dCoDAt6Ji2KK1ETQEoIx+y7uxjNFGh3nz+8/mFiN+rG0b9nvAEgPCTqbhD6Ib7C9hakDfPyui926ZhNHu4G25Bx7RFaSVqCkAZ8wLZj44FZhe3MCFIK+0WIliA+NB4boLZUQWvdfXefd0I7yKYGJD4WPj06gbR1SurRADZVRQQA9mpOxt+jFWo582ffjxT8TsmWGfBKQAM447p0+Q+d4fGImkhlN97vpjn42GvUTYaPXQpPO41ysgjvLT6SlAZ1pmaAlBGLmQnU/d9OFKhnjf//vSTjv/PoVE9G70PRwCA2/FT3jHPH50y5X04ej8zwewous01995XqARl5EN24jrvHyrU87xv33+w4p9XwQQAugkOtdAicCOMTE+Urq3mHtUpv/c2vxKUkQvZ8WQ6cIYqdOZ5/357Uhd/szU/vbf+SlBGLmTvtWjJpQh+/VIorusqjb/Zkp/eW38lrutyIWsrFgDQn0t/fln5FOz9slW+LmTb9mvf0bqcQm5e/1UKEl66zCALAJmPfUF1zUARyNeCbOYDX1zVLKtZtmCqWTYpZFfwKr+IZa8PtoJfBCqtwNiqBrZy7RrzLB1cxTbHLAs709hvV/CkftvqaYIIzmvfOd0JTDttaUu7soiE3SNLE7Hs5V4QNRg02kIPa+gjHmkZyMoUkChdIsg+Hm/LRI4XPsu2K+GAIbj4eK2eMh1IyLKa85CNG4PTq/3y1v5N6OafJowQoPCmVgrA7Xve1Erl2jXL0m9XFve4OpZdbBMIjmgLPayXe+D7xNATuxJ8b4JMAQnT5YxlGeAIBxVjXIQJ2lkGW2xjgJiInUaYRARMfcSHBfed0x2otByGhZkiPctiY+Uf0hbRsMbsjImHbIICZNOJIMvZS3Nhpd6JwWfZm1oJcJ5jUymysPEdbZ6QZSPfkNCYLEKkmEorwtzomLbg0wyy/gQsSz5yab5jMyATP8gjMcsGnE1QanwBsulEkOVsTOiasNtJ90K7cMXgplbCpnRMKhU88YnmfXrSGbSljTiG8ElCwghhHKAmG1zItqqw+M5E59OyLIsPuP+sTQNZBL1l5rJYXPkC5NNlBlkOy2KKRjH6pL66jiM/orkUR0ZR4l9i0a1kBAyvr5KQjWJdTLTxj2OWbRmWjeO9FUJWPp0QsuyNCV0TrPN0L7TLrcui2WEUOlCu7JS5c1lGc7nHOja59OcGLcwhYQQcr8Ql7lw2CWTZLMucB6ady8bMLlc9l5VKlxlkmSyL/bmN0SS+YnB9sMVfMWA3T/7H09V+eauE4SZRhMj6BuHJXTHAKscWPRKxLGtiILdiQOIHC5SWZWUKSJpOBFnOXprIjpflVMCyiwVLfODxddn9Kns2udNmN6cWUFnrVtTcgFgAlo6AFRB4Jlypjf3zizOX/WQuuaZclxX/Jw3Olwe19qMlWpeVSSeCrLo9Eil//TpttZduuwGqf/3iQ1bZHgn9jkEa5bPsuiV3kFW3R0K/Y5BGNctyIatuL4Rm2TSqWVbw65cq0SybRjXLZrORxs56L0rRP7+gIHBm8/9ltWhZWjRktRRMNGS1FEz+B2dIKSZnbVgEAAAAAElFTkSuQmCC" alt="" />

ok,所有的空格都没有了,需求完成!