len(replace not giving expected result

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

len(replace not giving expected result

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-04-01 : 08:29:07

hi
I have a data as below in a single column


a|b|c|d|||||||    |
a|b|c|d|||||||            |

I applied the below query to get the records which do not have 11 delimiter, however I am not getting the correct result can you plz help

may be because with replace the last | is getting replaced with '' and len is not counting the spaces


select  len(column0),len(replace(column0,'|','')),column0 from dbo.stage_table where (len(column0) - len(replace(column0,'|','')))<>11

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 08:38:43

What is the expected output?

Harsh Athalye
http://www.letsgeek.net/

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 08:46:13

"may be because with replace the last | is getting replaced with '' and len is not counting the spaces"

Correct, LEN() does not count any trailing spaces.

use DATALENGTH() instead?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-04-01 : 08:48:06

Oh Sorry Harsh,

I forgot to include it in code, the output should give me records where I do not have 11 '|' (delimiter) in column0

actually I have loaded the entire file into one column i,e. column0 and checkin for the delimter wheather they are =11 or not, if not then I have to clean that record

Regards
NeilCSE

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-04-01 : 08:50:20

quote:
Originally posted by Kristen

"may be because with replace the last | is getting replaced with '' and len is not counting the spaces"

Correct, LEN() does not count any trailing spaces.

use DATALENGTH() instead?

Hi Kirsten,

Datalength is also giving the same result.

Regards,
Neil

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 08:56:16

I don't get the same result!!


DECLARE	@MyString1	varchar(100),
	@MyString2	varchar(100)

SELECT	@MyString1	= 'a|b|c|d|||||||    |',
	@MyString2	= 'a|b|c|d|||||||            |'

SELECT	len(@MyString1),len(replace(@MyString1,'|','')),@MyString1
SELECT	DATALENGTH(@MyString1),DATALENGTH(replace(@MyString1,'|','')),@MyString1
SELECT  len(@MyString2),len(replace(@MyString2,'|','')),@MyString2
SELECT  DATALENGTH(@MyString2),DATALENGTH(replace(@MyString2,'|','')),@MyString2

----------- ----------- -------------------
19          4           a|b|c|d|||||||    |
----------- ----------- -------------------
19          8           a|b|c|d|||||||    |

----------- ----------- ---------------------------
27          4           a|b|c|d|||||||            |
----------- ----------- ---------------------------
27          16          a|b|c|d|||||||            |

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources