BookmarkSubscribeSubscribe to RSS Feed
Benson

Occasional Contributor

Joined:

Jun 4, 2018

How do I make text box to a List for sql query

I am trying to make a text box to allow users to input information for sql query, and users copy this information from excel to text box. my question:

1. This length of Lot is fixed =14, and there are 11 lots in this example. Why the length of text box is 176?

2. How to  make this text into a list as {"18148018-01-00","18148018-01-01","18148018-02-00"......}

Names Default To Here( 1 );
//make a text box for user input
New Window( "Example",
 Outline Box( "Lot List Input",
  H List Box( Text Box( "=>" ), teb = Text Edit Box( "Please input your lots", <<Set Width( 200 ), <<set nLines( 20 ) ) )
 )
);

length(teb<<get text);

 

 

 

1.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: How do I make text box to a List for sql query

The issue is, that the paste buffer that you copied into from your Excel table, contains Carriage Returns(hex 0D) and Linefeed(hex 0A) at the end of each cell that was copied.  Below is the Hex representation of the list of lots, taken from the pasted Excel file.  It is actually all one long line of hex characters, but for illustration, I have broken the line of hex characters at the end of each lot

31383134383031382D30312D30300D0A
31383134383031382D30312D30310D0A
31383134383031382D30322D30300D0A
31383135383031312D30312D30300D0A
31383135383031312D30322D30300D0A
31383138383030322D30312D30300D0A
31383138383030322D30312D30320D0A
31383138383030322D30312D30330D0A
31383138383030342D30312D30300D0A
31383439383030372D30312D30300D0A
31383441383030352D30312D30300D0A

 Thus, 

176 = 11 X (14 + 2)

 

Here is a piece of code that will break the string into a list of strings

fullText = teb << get text;
fullTextList = {};
start = 1;
While( Substr( fullText, start, 14 ) != "",
	Insert Into( fullTextList, Substr( fullText, start, 14 ) );
	start = start + 16;
);
show( fullTextList );
Jim
pmroz

Super User

Joined:

Jun 23, 2011

Solution

Re: How do I make text box to a List for sql query

Here's another way to parse the information using WORDS.  Followed by using CONCAT ITEMS to create an IN list.

lot_text = 
"31383134383031382
31383134383031382
31383134383031382
31383135383031312
31383135383031312
31383138383030322
31383138383030322
31383138383030322
31383138383030342
31383439383030372
31383441383030352";
crlf = "
";
lot_list = words(lot_text, crlf);
lot_in_list = "('" || concat items(lot_list, "', '") || "')";
4 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: How do I make text box to a List for sql query

The issue is, that the paste buffer that you copied into from your Excel table, contains Carriage Returns(hex 0D) and Linefeed(hex 0A) at the end of each cell that was copied.  Below is the Hex representation of the list of lots, taken from the pasted Excel file.  It is actually all one long line of hex characters, but for illustration, I have broken the line of hex characters at the end of each lot

31383134383031382D30312D30300D0A
31383134383031382D30312D30310D0A
31383134383031382D30322D30300D0A
31383135383031312D30312D30300D0A
31383135383031312D30322D30300D0A
31383138383030322D30312D30300D0A
31383138383030322D30312D30320D0A
31383138383030322D30312D30330D0A
31383138383030342D30312D30300D0A
31383439383030372D30312D30300D0A
31383441383030352D30312D30300D0A

 Thus, 

176 = 11 X (14 + 2)

 

Here is a piece of code that will break the string into a list of strings

fullText = teb << get text;
fullTextList = {};
start = 1;
While( Substr( fullText, start, 14 ) != "",
	Insert Into( fullTextList, Substr( fullText, start, 14 ) );
	start = start + 16;
);
show( fullTextList );
Jim
Benson

Occasional Contributor

Joined:

Jun 4, 2018

Re: How do I make text box to a List for sql query

Jim, Thanks for explantion. I  consider there will be 2 extra characters form excecl with copy/paste, and Now I could make a list :)

pmroz

Super User

Joined:

Jun 23, 2011

Solution

Re: How do I make text box to a List for sql query

Here's another way to parse the information using WORDS.  Followed by using CONCAT ITEMS to create an IN list.

lot_text = 
"31383134383031382
31383134383031382
31383134383031382
31383135383031312
31383135383031312
31383138383030322
31383138383030322
31383138383030322
31383138383030342
31383439383030372
31383441383030352";
crlf = "
";
lot_list = words(lot_text, crlf);
lot_in_list = "('" || concat items(lot_list, "', '") || "')";
Highlighted
Benson

Occasional Contributor

Joined:

Jun 4, 2018

Re: How do I make text box to a List for sql query

This is useful :D