space in string in one field - 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

General SQL Server Forums

New to SQL Server Programming

space in string in one field

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-07-07 : 14:29:54

I am writing a query as below and in the result I see spaces in some filed:

select

(OGIDX + DGIDX + MIDX + CIDX) IDX_C

From [dev].[GAMS_Init_Var_MDF]

As you can see it is only one filed in a column but there is space between M.... and C... in some fields but not in all fields:

IDX_C
O40100000D20000450M30000003 C30000019
O40100000D20000450M20000002 C30000020
O40100000D20000280M10000000C30000017

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 14:31:46

Probably because there are spaces in the individual columns. You can strip of the spaces:


select 

replace((OGIDX + DGIDX + MIDX + CIDX),' ','') IDX_C

From [dev].[GAMS_Init_Var_MDF]

goligol
Posting Yak Master

128 Posts

Posted - 2011-07-07 : 17:29:18

Do you mean:
((OGIDX + DGIDX + MIDX + CIDX)+' '+'') IDX_C

instead of

((OGIDX + DGIDX + MIDX + CIDX),' ','') IDX_C

anyway none of them is working, the first has no error but hte space problem is still there, the second command gives error.

Thank you

sshelper
Posting Yak Master

216 Posts

Posted - 2011-07-08 : 01:09:50

One possible cause of that extra space is that it is not really a space but an unprintable character like a carriage return or line feed. To verify if this is the case, try running this query and see what value you get. You should get a value of 32 for all rows. If not, then it is not a space but an unprintable character.

SELECT ASCII(RIGHT(CIDX, 1))
FROM [dev].[GAMS_Init_Var_MDF]

Regards,
SQL Server Helper
http://www.sql-server-helper.com/sql-server-2008/sqlconnection-connection-string.aspx

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-08 : 02:50:59

select ltrim(rtrim(OGIDX))+ltrim(rtrim(DGIDX))+ltrim(rtrim(MIDX))+ltrim(rtrim(CIDX)) as IDX_C from <YOURTABLE>

--Ranjit

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-08 : 03:06:23

quote:
Originally posted by goligol

Do you mean:
((OGIDX + DGIDX + MIDX + CIDX)+' '+'') IDX_C

instead of

((OGIDX + DGIDX + MIDX + CIDX),' ','') IDX_C

anyway none of them is working, the first has no error but hte space problem is still there, the second command gives error.

Thank you

Sunita means EXACTLY what she has posted and there can't be an error.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources