Column char - problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Column char - problem

Author

Topic

DeepakNewton
Starting Member

39 Posts

Posted - 2008-11-06 : 02:02:51

Hello All,
I have encounter wiered problem where i am unable to rectify
I got a cloumn with datatype of char(3) null, there are rows in the table with the column value as empty space , But when i tried to pull the data from the table using the below sample Query
Select cloumn1 , column2 from Table where Column1 = ''
The row is not returned and i checked the table but the row with empty column space is existing

Any Idea ?

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-06 : 02:17:54

select len(column1)
from table

Find out whether its actually empty or not. There may be a space. Did you try trimming?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 02:40:06

use ltrim & rtrim for trimming.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 03:01:27

SELECT ASCII(SUBSTRING(Col1, 1, 1)), ASCII(SUBSTRING(Col1, 2, 1)), ASCII(SUBSTRING(Col1, 3, 1))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

DeepakNewton
Starting Member

39 Posts

Posted - 2008-11-06 : 03:26:36

Peso
I tried with the Query, it returns as 0 0 0 ,
Here is my Query
SELECT
pktNumberID
, PN_FD
, PS_FD
, PM_FD
, PS_FD + PN_FD + PM_FD as BNumber

FROM
Table1
WHERE
pkNumberID = 123
my problem is i use to retrieve the column PS_FD and concatenate with another columns PN_FD , PM_FD (all columns are char data type) at that time some of the rows having empty column PS_FD returns and result is like " BWASSSIO 00" , but some peculiar rows with column which also has empty is returned as empty while concatenating..... This gives me a problem.......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 03:28:56

[code]SELECT pktNumberID,
PN_FD,
PS_FD,
PM_FD,
RTRIM(PS_FD) + RTRIM(PN_FD) + RTRIM(PM_FD) AS BNumber
FROM Table1
WHERE pkNumberID = 123[/code]

E 12°55'05.63"
N 56°04'39.26"

DeepakNewton
Starting Member

39 Posts

Posted - 2008-11-06 : 03:49:19

Peso,
After Using the Above Query also it reult of BNumber is same reulting empty
PKNumber PN_FD PS_FD PM_FD BNUMBER
11908 8166145 00
11908 8617712 BSF 00 CMB861771200

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 03:57:00

Any NULL possibilities you haven't told us about?

SELECT	pktNumberID,
	PN_FD,
	PS_FD,
	PM_FD,
	RTRIM(COALESCE(PS_FD, '')) + RTRIM(COALESCE(PN_FD, '')) + RTRIM(COALESCE(PM_FD, '')) AS BNumber
FROM	Table1
WHERE	pkNumberID = 123

E 12°55'05.63"
N 56°04'39.26"

DeepakNewton
Starting Member

39 Posts

Posted - 2008-11-06 : 04:24:20

Peso ,
Even after using the Above Query its still resulting Empty in BNumber
I checked the table thru the entrprisemanager and that particular column has Empty space occupied
thnks
Deepak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 04:38:48

Use both LTRIM and RTRIM.

E 12°55'05.63"
N 56°04'39.26"

DeepakNewton
Starting Member

39 Posts

Posted - 2008-11-06 : 04:45:32

I have used Ltrim and Rtrim also, there is something occupied in the column which shows blank when i checked the enterprise manager

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 04:47:05

Are those "hard spaces", ie ASCII 160?
In that case, you should have noticed when running my suggestion posted at 11/06/2008 : 03:01:27

E 12°55'05.63"
N 56°04'39.26"

DeepakNewton
Starting Member

39 Posts

Posted - 2008-11-06 : 05:08:29

Even that Query Return as 0 0 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 05:21:02

[code]DECLARE @Sample TABLE
(
data CHAR(3)
)

INSERT @Sample
SELECT NULL UNION ALL
SELECT '' UNION ALL
SELECT 'e' + CHAR(160) + 'e' UNION ALL
SELECT 'pe ' UNION ALL
SELECT 'dw' + CHAR(160) UNION ALL
SELECT 'yak'

SELECT data,
ASCII(SUBSTRING(data, 1, 1)),
ASCII(SUBSTRING(data, 2, 1)),
ASCII(SUBSTRING(data, 3, 1)),
'_' + LTRIM(RTRIM(data)) + '_'
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

DeepakNewton
Starting Member

39 Posts

Posted - 2008-11-06 : 06:58:53

Peso,
Below Query is working , thanks for ur Suggestion/Key inputs About ASCII
SELECT pktNumberID,
PN_FD,
PS_FD,
PM_FD,
Replace(PS_FD,CHAR(0),'')) + PN_FD + PM_FD AS BNumber
FROM Table1
WHERE pkNumberID = 123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources