Determine character in database field

Question

Post reply

Determine character in database field

Terry_Pino

Ten Centuries

Points: 1253
More actions
August 15, 2004 at 7:26 pm

#181487

Hi All,
How can I tell what the contents of a field are? We have some records which seem to be causing trouble on a particular field(GivenName). When I look at the record via enterprise manager the field looks empty i.e. it does not say <Null>. I used used transact sql to try determine what was in the field but the statement 'select ascii(GivenName) from table where id = 12345' comes back blank(not null). The statement 'select len(GivenName) from table where id = 12345' returns 0. Im stumped!!! Any suggestions would be much appreciated.
Regards,
Terry

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

Could it be a zero-length string? Try running

select *

from table where id = 12345 and GivenName = ''

If the record is still returned, you've cracked it.

Regards

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Terry_Pino Ten Centuries Points: 1253 More actions · Answer 2

Yes this does return the record.....but Im curious as to why the problem has surfaced in the last month and only for new records. This process has been going on for two years without a hitch. Could it be coincidental that new hardware has been rolled out recently? Is there a way to test for special characters which may not be visible?

Thanks

Terry

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

Sounds like a process/application change to me. Hardware should have nothing to do with this.

As far as testing for invisible characters is concerned, you were on the right track - use the

WHERE LEN(fieldname) > 0

syntax to find dodgy stuff. This should capture everything apart from zero-length strings and NULLs (to my knowledge).

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 4

What is the data type of the column.

Try giving DATALENGTH rather len.

Do this if you running the query output to grid.

Run the query, after pressing Ctrl + T (result in text)

and check what's that character.

Thanks,

Ganesh

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

I must admit - I just assumed that a field called GivenName would be a string

DATALENGTH is a more general function, as it accepts arguments of any data type (LEN requires a string argument).

Regards.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Terry_Pino Ten Centuries Points: 1253 More actions · Answer 6

Im not getting anywhere, I'd better start from the other end.....We are opening a text file in Excel which was exported from an SQL database using predefined widths for each field. When I open the text file in Excel, the records causing problems seem fine i.e. Each of the fields are aligned.

However, when I open the file in Excel, what happens is that records where the GivenName is blank seem to be grabbing the first letter of the Surname field, hence throwing the rest of the fields for that record out of whack.

Im just totally stumped why this has started occuring now. Or maybe, it has always been occuring and no one noticed until now? I dont think this is very likely however.

Im struggling to come to a solution. Help

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

Presumably you have a reason for using fixed-width files rather than [tab] or [comma] delimited? A mainframe background, perhaps

When you open the fixed-width file in Excel, are you using the 'Tools/Text to Columns' feature to split it into separate fields? If you are, and it's still failing, it suggests that the extract file is not uniformly fixed-width after all.

It would probably help if you could post the query that generates the export file.

Regards

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Terry_Pino Ten Centuries Points: 1253 More actions · Answer 8

Another piece to the puzzle....the import works fine in Access 2000. Doh!

Terry_Pino Ten Centuries Points: 1253 More actions · Answer 9

Hi Phil

The method for creating hasnt changed. The application front end offers the user the type of delimiter they want, then a bcp call is made.

set @strCommandText = 'bcp "select ' + @strColumnList + ' from ' + @strDatabaseName + '..' + @strTableName + ' with (READUNCOMMITTED)" queryout "K:\BCP\' + @strContactListFileName + '.txt" -S' + @strServerName + ' -U' + @strUserName + ' -P' + @strUserPassword + ' -c -t' + @strFileSeparator + ' -r\n'

Thanks,

Terry