hidden data in char field?

[mdlaugh1]

We have a table containing several Char type fields. Two of these fields 'appear' to be empty when viewed through query with a Select * from tblMeetingRooms.

However when including a WHERE clause on that field i.e. WHERE (strAmenities like '2 chairs. Table.%') it pulls the record. And when viewed on the website it does display the text of '2 chairs. Table'.

Is it possible to hide values in a field? Why don't I see the data? I need to change the text in the field and don't know how to get to it.

I have a jpg of the sql output but I do not see how / where to attach it on this post...
thanks!!

 

[ptheriault]

Select * from tblMeetingRooms
where CharIndex(Char(9), column_name) > 0

You'll probably want to check Char(9) (Tab Character) as well as Char(10) and Char(13), (line feed & Carriage return).

*We must say a big thanks to George for this information!

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

The SQL Enterprise Manager isn't made for being a data editor. It will often hide data if the value is longer than some predefined setting (that you can't change).

You will need to use an update statement to change the value.

The problem is probably cropping up because you are using CHAR instead of VARCHAR so you have to deal with the extra spaces in the field.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[monksnake]

I JUST answered a question referring to Char(10) and Char(13), spooky.

<.

 

[ptheriault]

look at my bad data thread in onpnt.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mdlaugh1]

Please bear with me.. a little new to some SQL db stuff,,,

I have a Description field char 500.
one record contains the following - and it shows up in the query:

"Rosemeade Swim Complex includes a 50-meter Olympic size pool with a 25-meter short course, separate diving well, training pool and a wading pool. The complex is located at 1330 Rosemeade Parkway."

However, the Amenities field is char 1000. It appears blank and contains the following:
"2 chairs. Table. No fee for use. Use is on first-come basis."

'Amenities' is a much shorter text string.

When I did the check for "CharIndex(Char(9), column_name) > 0" I didn't pull records, but checking for char(10) and char(13) pulls several including the one I'm looking for.

But I don't understand what this means or how to change the text.
thanks!!!

 

[monksnake]

Good call Paul.

<.

 

[gmmastros]

I would suggest you check your options for query analyzer. By default, it only shows the first 255 characters of a field.

Here's what you do...

Open Query Analyzer
Click Tools -> Options
Click the 'Results' tab

Change the value for 'Maximum Characters Per Column'.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

CHAR(10) and CHAR(13) are the carriage return and new line (maybe not in that order). Your value may actually be:

CHAR(10)CHAR(13)2 chairs. Table. No fee for use. Use is on first-come basis.

So, it would be 'hidden' because of the carriage return and new line (and I can never remember which CHAR is which).

-SQLBill

Posting advice: FAQ481-4875

 

[mdlaugh1]

George,
Thanks! I've changed Query Analyzer Max characters value and now i see values for Amenities. i guess these are Right Justified, maybe??

Can I change the value in the field in some way in Query Analyzer like I can in SQL Enterprise? When I go back to Enterprise I still see the blank column....

thanks!

 

[monksnake]

The Char(13), carriage return is the culprit.


char(9) - tab will space your column and running a query like you did above (WHERE (strAmenities like '2 chairs. Table.%')) would return nothing if there are tabs at the beginning.

and char(10) - linefeed will not show any difference in your data, so you would be able to see it at the beginning of the column.


I think if you look more closely at your returned rows in query analyzer, you'll see that the content is a line or 2 below the rest of the row's data.

<.

 

[SQLSister]

BTW using Char as a datatype for long things like description might not be the best thing either. if the description is 100 characters long and you use char (500) as the datatype it will store 500 characters adding spaces at the end to fill up the space. You might consider using varchar datatypes instead.

Questions about posting. See faq183-874

 

[SQLSister]

to change the field in query analyzer you need to run an update statement. Make sure you want to get rid of those special characters before you do though. Sometimes people add them to long descriptions to make them easier to read.

Questions about posting. See faq183-874

 

[ptheriault]

if you want to get rid of them you will use the replace function

update tblMeetingRooms.
set strAmenities = replace(strAmenities,char(13),' ')
where CharIndex(Char(13), strAmenities) > 0

this will replace the carriage return with a space.

I would make a backup of my table before I ran this too.
You can do that like this.

SELECT * INTO tblMeetingRooms_BAK
FROM tblMeetingRooms

[small] *Thanks George. [/small]

- Paul

- If at first you don't succeed, find out if the loser gets anything.