formating of String results.

I have the following Code (see below). It works fine except after the first quote a return is inserted.  What I want to do is take this string and use it as part of a where clause but I need the proper format.  Below is the current results.
DLID = '94045' DLID = '
16070' DLID = '
17136' DLID = '
27793' DLID = '
45871' DLID = '
76162' DLID = '
65493' DLID = '
17881' DLID = '
85790' DLSID = '
110509' DLID = '
84701'

This is what I want:

DLID = '94045' DLID = '16070' DLID = '17136' DLID = '27793' DLID = '45871' DLID = '76162' DLID = '65493' DLID = '17881' DLID = '85790' DLID = '110509' DLID = '84701'

Hope this make sense. 
declare @LTable table  (
		LTableID int Identity(1,1) Primary key not null,
		LNum varchar(25))


   DECLARE @position int, @string NVARCHAR(MAX),@separator NCHAR(1), @Value varchar(max);
Declare @Cnt int, @MaxCnt int, @BuildWhere varchar(max);
	set @string = '94045
16070
17136
27793
45871
76162
65493
17881
85790
110509
84701'
   SET @position = 1
	set @separator = CHAR(13)
	set @Cnt = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
set @Value = substring(@string, @position, charindex(@separator,@string,@position) - @position)
         INSERT into @LTable
         SELECT @Value
         SET @position = charindex(@separator,@string,@position) + 1

if @Cnt = 1 
set @BuildWhere = ' DLID = ' + '''' + @Value + ''''
else
set @BuildWhere = @BuildWhere + ' DLID = ' + '''' + @Value + ''''

set @Cnt = 2

END

Print @BuildWhere

Open in new window

MadIceAsked:
Who is Participating?
 
UnifiedISConnect With a Mentor Commented:
Use char(10) and replace

declare @LTable table  (
LTableID int Identity(1,1) Primary key not null,
LNum varchar(25))


DECLARE @position int, @string NVARCHAR(MAX),@separator NCHAR(1), @Value varchar(max);
Declare @Cnt int, @MaxCnt int, @BuildWhere varchar(max);
      set @string = '94045
16070
17136
27793
45871
76162
65493
17881
85790
110509
84701'
   SET @position = 1
      set @separator = CHAR(13)
      set @Cnt = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
      set @Value = substring(@string, @position, charindex(@separator,@string,@position) - @position)

INSERT into @LTable
         SELECT @Value
         SET @position = charindex(@separator,@string,@position) + 1

if @Cnt = 1
set @BuildWhere = ' DLSID = ' + '''' + REPLACE(@Value, CHAR(10), '') + ''''
else
set @BuildWhere = @BuildWhere + ' DLSID = ' + '''' + REPLACE(@Value, CHAR(10), '') + ''''

set @Cnt = 2
END

Print @BuildWhere
0
 
Ephraim WangoyaCommented:
Use replace to get rid of the Carriage return
declare @LTable table  (
		LTableID int Identity(1,1) Primary key not null,
		LNum varchar(25))


   DECLARE @position int, @string NVARCHAR(MAX),@separator NCHAR(1), @Value varchar(max);
Declare @Cnt int, @MaxCnt int, @BuildWhere varchar(max);
	set @string = '94045
16070
17136
27793
45871
76162
65493
17881
85790
110509
84701'
   SET @position = 1
	set @separator = CHAR(13)
	set @Cnt = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
set @Value = substring(@string, @position, charindex(@separator,@string,@position) - @position)
         INSERT into @LTable
         SELECT @Value
         SET @position = charindex(@separator,@string,@position) + 1

if @Cnt = 1 
set @BuildWhere = ' DLID = ' + '''' + @Value + ''''
else
set @BuildWhere = @BuildWhere + ' DLID = ' + '''' + @Value + ''''

set @Cnt = 2

END

SET @BuildWhere = REPLACE(@BuildWhere, CHAR(13), ' ')

Print @BuildWhere

Open in new window

0
 
MadIceAuthor Commented:
ewangoya. Tried that and got the same results. Tried this too
SET @BuildWhere = REPLACE(@BuildWhere, CHAR(13), '')
without the space.

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Start This Course
 
Ephraim WangoyaCommented:
replace char(10) also
declare @LTable table  (
		LTableID int Identity(1,1) Primary key not null,
		LNum varchar(25))


   DECLARE @position int, @string NVARCHAR(MAX),@separator NCHAR(1), @Value varchar(max);
Declare @Cnt int, @MaxCnt int, @BuildWhere varchar(max);
	set @string = '94045
16070
17136
27793
45871
76162
65493
17881
85790
110509
84701'
   SET @position = 1
	set @separator = CHAR(13)
	set @Cnt = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
set @Value = substring(@string, @position, charindex(@separator,@string,@position) - @position)
         INSERT into @LTable
         SELECT @Value
         SET @position = charindex(@separator,@string,@position) + 1

if @Cnt = 1 
set @BuildWhere = ' DLID = ' + '''' + @Value + ''''
else
set @BuildWhere = @BuildWhere + ' DLID = ' + '''' + @Value + ''''

set @Cnt = 2

END

SET @BuildWhere = REPLACE(REPLACE(@BuildWhere, CHAR(13), ' '), CHAR(10), ' ')
print @BuildWhere

Open in new window

0
 
Ephraim WangoyaConnect With a Mentor Commented:
use this

SET @BuildWhere = REPLACE(REPLACE(@BuildWhere, CHAR(13), ' '), CHAR(10), '')
0
 
MadIceAuthor Commented:
Both of these answers worked.  UnifiedIS just had to remove a set of quotes. Thanks so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here