Attempting to identify carriage return line feed character in SQL field

[Stain555]

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.

[#2]

@Stain555 

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. 

[#3]

Hi 

For info, Chr(10) is line feed & Chr(13) is carriage return. See ASCII Codes Table 

[#4]

OP may have to switch to text import and possibly add a column updating to only specific ASCII characters - Shift+Space (used to be called a hard space) comes to mind.

[#5]

I actually managed to use an online vb script to remove the 1st vbCr and vbLf after concatenating my data. It didn't seem to work when called from a SQL query but I can live with it in the context of my application. The concatenated data no longer has a blank line at the beginning.

[#6]

@Stain555 

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.

Thank you.