Problem with query - 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 2008 Forums

Transact-SQL (2008)

Problem with query

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-07 : 14:51:10

DECLARE @str2 VARCHAR(100)
DECLARE @RTNSTR Varchar(max)
DECLARE @TestString Varchar(18) = 'abc*d'
Set @str2 = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 &-./'
declare @i int
Set @i = 0

While @i < = len(@TestString)
BEGIN
if Charindex(substring(@TestString,@i,1),@str2) =0
SET @RTNSTR = @RTNSTR + substring(@TestString,@i,1)
ELSE
SET @RTNSTR = @RTNSTR + SPACE(1)
SET @i = @i + 1
End
Select @RTNSTR

@RTNSTR is NULL when it should be 'abc d'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-07 : 16:56:43

You need to initialize the @RTNSTR string.

DECLARE @str2 VARCHAR(100) 
DECLARE @RTNSTR Varchar(max)
DECLARE @TestString Varchar(18) = 'abc*d'
Set @str2 = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 &-./'
declare @i int 
Set @i = 0

SET @RTNSTR = '';
While @i < = len(@TestString)
BEGIN
if Charindex(substring(@TestString,@i,1),@str2) =1

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-07 : 19:08:02

quote:

Originally posted by sunitabeck

You need to initialize the @RTNSTR string.

DECLARE @str2 VARCHAR(100) 
DECLARE @RTNSTR Varchar(max)
DECLARE @TestString Varchar(18) = 'abc*d'
Set @str2 = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 &-./'
declare @i int 
Set @i = 0

SET @RTNSTR = '';
While @i < = len(@TestString)
BEGIN
if Charindex(substring(@TestString,@i,1),@str2) =1

Hi This only returns 'a' and not 'abc d'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-07 : 20:45:49

I was only showing part of the query where I thought you needed to make changes. The whole query would be:

DECLARE @str2 VARCHAR(100) 
DECLARE @RTNSTR Varchar(max)
DECLARE @TestString Varchar(18) = 'abc*d'
Set @str2 = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 &-./'
declare @i int 
Set @i = 0

SET @RTNSTR = '';
While @i < = len(@TestString)
BEGIN
if Charindex(substring(@TestString,@i,1),@str2) > 0
SET @RTNSTR = @RTNSTR + substring(@TestString,@i,1)
ELSE
SET @RTNSTR = @RTNSTR + SPACE(1)
SET @i = @i + 1
End
Select @RTNSTR

Edit: Also, had to change the = 0 to > 0

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-07 : 21:03:34

Brilliant, Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources