![]() ![]() SET column_name = SUBSTRING(column_name,0, CHARINDEX( CHAR(10)+ CHAR(10), column_name)) This update statement will clean and update a column storing a string contaminated like the above example. My solution, in this instance, where we have multiple formatting characters is to look for the first instance of two identical characters and then keep all preceding information. Yes, you can use it along with the function REVERSE if you are using SQL 2008+ but it’s not very nice. The function PATINDEX does accept a ‘% like’ statement, but finds the first occurrence. Finding the end of a string is not straightforward in SQL as most functions only take an expression such as ‘foo’ and not a pattern. This will remove ALL entries so the users will be calling up complaining their formatting has gone.Īlternatively you need to find the LAST valid text and remove the remainder. To clean this up you could blaze in and use the function REPLACE SELECT REPLACE(column_name, CHAR(13) + CHAR(10), '') In a perfect world this would be captured during input (see end of article), but if some slips through the net you need to be able to clean the text string within the SQL database when faced with a situation like this. Especially when their text is nicely arranged with multiple spaces, cartridge returns and line feeds amongst other special character formatting. ![]() Data entry, it’s where users love to copy and paste.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |