May 20 2021 06:13 AM
May 20 2021 06:13 AM
I have a column on a table that appears to have some form of the carriage return/line feed character in the first byte. I have discovered this when I concatenated this with other related text columns (stepped through code and the start of the concatenated text is simply this column value, no code error adding anything extra). The resultant concatenated field is definitely skipping a line at the beginning as SQL queries that access this new text have an extra blank line above the first words.
I have tried simply to use many of the vbCr/vbLf update functions/subs online to remove this but no change occurred. So I thought that there must be a way of displaying a string in binary or hex, so that I can see what EBCDIC/ASCII or other character is at the front of the text but the SQL Hex function can only be used on numerics. Anyone know a means of finding out what special white space character is in my text? I do have other carriage return/line feeds in the concatenation which are probably the same type (original data was imported from Excel) but I just want to get rid of this top blank row.
May 20 2021 08:32 AM
You can search for hidden characters using their ASCII codes, for line feed and carriage return they are Chr(13) and Char(10) --although I never remember which is which.
Instr([YourTextFieldStringNameGoesHere], Chr(13)) will return a value greater than 0 if there is an instance of Chr(13) in that field, for example.
May 20 2021 09:46 AM
For info, Chr(10) is line feed & Chr(13) is carriage return. See ASCII Codes Table
May 21 2021 03:50 PM
May 22 2021 05:07 AM
May 22 2021 03:32 PM
While it's good to know you found a solution, questions remain. Sometimes, it can be useful to share solutions so that others looking for help can possibly benefit. Would that be possible? Can you share this "online vb script" and how you used it?
Also, "...It didn't seem to work..." doesn't tell us much about the problem in SQL. How did you attempt to implement it in SQL and what happened -- or didn't happen? Or was there an error?
Again, while it's helpful to share that you found a solution, it would be nice to share it as well, in case someone else can benefit later.