Wikipedia:Reference desk/Archives/Computing/2019 November 12

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

November 12

What does Android `fastboot erase` command do?

Almost all Android tutorials recommend that command before applying a fastboot flash partition path_to_image without explaining what it does. Those which do explain just say that it "erase" the partition, which is totally meaningless. In the world of computing, there is no such thing, you only write (and overwrite) to a location. The manual page does not help either. From `man fastboot`:

fastboot erase partition
     Erase a flash partition.

Does anyone know what does that command actually do at low level? Why do we just flash the new image over? -- Livy (talk) 12:59, 12 November 2019 (UTC)

I would assume that "erase" means to overwrite with zeroes. SinisterLefty (talk) 13:49, 12 November 2019 (UTC)

The official documentation states that "erase" will "Erase the indicated partition (clear to 0xFFs)".

It's actually very unlikely to that this writes 0xFF data to the storage media. On a modern software/hardware device - especially an Android instance running on a mobile device with NAND "flash" storage - an "erasure" of a storage partition is more likely some form of key invalidation procedure.

Have a look at our Wikipedia article on flash file systems, and data encryption from the official Android documentation - specifically the hardware backed keystore.

And for the gory technical details, read fastboot.cpp from the master Android open source repository. fastboot erase runs code in the fastboot file system generator, which is probably implemented in your device-specific board support package - probably by interacting with some vendor-specific hardware features. If you're running the reference open-source implementation, it looks like the officially-supported file system is backed by OpenSUSE-style ext2 from the genext2fs project.

Nimur (talk) 20:21, 12 November 2019 (UTC)

In the world of computing, there is no such thing, you only write (and overwrite) to a location. Actually, there is a difference between erasing and writing in Flash technology. Using NAND Flash, you can only write to a block of Flash that has previously been erased. You cannot just overwrite data like you can on a hard disk. A complicating factor is in most devices the "erase block size" is larger than the "write block size", so you must erase a large block (say 256K) and then you can write smaller subsections of it (say 16K blocks). This is why Flash filesystems are different than filesystems on overwritable media. I'm not sure exactly how fastboot works; I would expect "fastboot flash" to erase the partition before writing it, but if for some reason it does not do that then the "fastboot erase" command would be mandatory. CodeTalker (talk) 21:21, 12 November 2019 (UTC)

Oracle SQL Substring function

I have a text field that I need to extract two values out of. That usually requires a SUBSTR function but, in this case, the position of those values is variable. The field looks like this: PO#1-123456 Lines:19 Pieces:182 however it could just as easily look like this: PO#1-654321 Lines:312 Pieces:4832, which obviously complicates things. I'm currently extracting the values after the entire field is passed into Excel, but I'd prefer to do it within the query so that I can SUM them up in there and reduce the data footprint in my file. I know the first value will begin at the 21st character and I know the second value will end with the last character, so in Excel I'm doing this for the first one: =VALUE(MID([@REFERENCE],21,FIND("Pieces",[@REFERENCE])-21)) and this for the second one: =VALUE(RIGHT([@REFERENCE],LEN([@REFERENCE])-FIND("Pieces",[@REFERENCE])-6)) Is it possible to do the same thing within SQL - or do I need to figure out some kind of wild regex statement? Matt Deres (talk) 21:00, 12 November 2019 (UTC)

I'm not quite following what you are looking for. The word "Pieces" is apparently part of both strings. So:

• For the first string, does it start at character 21 and end in first occurrence of the word "Pieces" ? If not, how do you know when it ends ?

• For the second string, does it start after the last occurrence of the word "Pieces" and continue to the end of the record ? If not, then how do you know where it starts ? SinisterLefty (talk) 21:42, 12 November 2019 (UTC)

@SinisterLefty: My feeling is that the 'first string' is the contiguous block of digits starting just after the first colon, similarly the 'second string' is a digits-only substring after the second colon. If I'm right, that calls for regex, alas I don't know if SQL supports it. --CiaPan (talk) 22:37, 12 November 2019 (UTC)

Sorry, I should have been more explicit. The field has a PO# and the number of lines and the number of pieces on that PO. I want to extract the number of lines and the number of pieces, but they're not always at the same position in the string since the number of lines could be 1 or 2 or more characters - and so could the number of pieces. Because I know the lengths of all the other areas of the field, I can use Excel's LEN, FIND, RIGHT, and MID functions to do the operation in Excel. What I'd like to know is whether SQL can do the same kinds of operations. In answer to CiaPan, yes, Oracle SQL supports regex, but I don't even qualify as a beginner at regular expressions. So, I'd prefer a purely SQL answer, but I'll take whatever I can get. :-) Matt Deres (talk) 13:28, 13 November 2019 (UTC)

Please tell us exactly what search you are doing now. That is, what delimiters you are searching for, etc. We don't so much need the high level info of what the data represents, as we need the nuts and bolts of how you currently extract it. We don't all know how to use those Excel expressions. Some examples would help, too, showing the most extreme cases (longest and shortest). SinisterLefty (talk) 16:36, 13 November 2019 (UTC)

I provided that in my initial post. As I said, I'm doing all the work within Excel right now; today, the SQL is literally just a select statement from the transaction table where the field in question (called REFERENCE) sits. That field is exactly as I've described above: an alphanumeric field with a variable length of text containing two chunks that I'd like to extract (and sum, but that's the easy part). As a practical thing, the number of lines could run from 1 to 9999 and the number of pieces is maybe 1 to 99999. If I limit my query to a week, I may get a few hundred lines returned (because a few hundred purchase orders meet the other criteria of the query) and those go directly into Excel via MS Query. Those Excel formulas in the OP get me what I need today, but the amount of data returned bloats the file, so I'd like to do the parsing and summing within the query. Matt Deres (talk) 18:10, 13 November 2019 (UTC)

OK, those ranges help. Are you sure the purchase order won't ever have another digit ? You could easily have the query only return data from the 21st character on, and do the rest of the processing using Excel. That would help a bit. Or else use a regular expression to parse it in SQL. Another possibility is that you could select a range that would include the number you want and also some text, and then convert that to an integer with SQL. Character ranges might be 21-24 and 26-EOL, or (EOL-5)-EOL. I suspect that this would have the effect of discarding the non-numeric portion, but, depending on the SQL implementation, it might also produce an error. Worth a try, though ! Be sure to test with all 4 combos of min and max length. SinisterLefty (talk) 18:57, 13 November 2019 (UTC)

I'll give that a go when I'm back at that computer. When you say to convert the range to an integer, do you mean to use a TO_NUMBER function or something else? Matt Deres (talk) 20:35, 13 November 2019 (UTC)

Both the TO_NUMBER and INT functions error-out if any non-numbers are included, so that won't work, but I appreciate the attempt. :-) Matt Deres (talk) 13:49, 14 November 2019 (UTC)

Too bad, would have made for a much simpler solution if it worked. I see you got your full answer below, at any rate. SinisterLefty (talk) 09:02, 15 November 2019 (UTC)

@Matt Deres: As the page you linked describes (but does not explain), a regular expression like [0123456789] is a list that matches any character being a decimal digit. With a range operator (being a dash) we can shorten it to [0-9]. When we append a one-or-more quantifier (which is a plus) we get an expression like [0-9]+, which matches any, non-empty string of digits. Browse through sections Metacharacters Supported in Regular Expressions and Constructing Regular Expressions for more definitions and examples.
Accordnig to the subsection 'Matching Character List' in the linked Oracle manual page, POSIX character classes are allowed in character lists. If so, you could also use longer, but more descriptive expression [[:digit:]]+ (the inner pair of brackets, together with colons, make a reference to a digit character class, the outer pair of brackets defines a list). Possibly you can also replace it with a \d shorthand, as described in Regex Tutorial - POSIX Bracket Expressions. That would look like [\d]+, but I do not know if it would actually work in Oracle SQL.
Once we're ready with our regexp, we need to search it in your data string str. See the Oracle Database SQL Functions for Regular Expressions section of the manual page – it describes the REGEXP_SUBSTR function as returning the actual substring matching the regular expression pattern you specify. See the detailed description of REGEXP_SUBSTR linked there. You will need an SQL expression like REGEXP_SUBSTR( str, '[0-9]+', pos, N ) to retrieve an N-th substring of digits from the value of str, found starting from position pos. The position to start the searching can be defined as the position of the first or the second colon, respectively, and calculated with the INSTR function: INSTR( str, ':', 1, 1 ) finds the first colon, and INSTR( str, ':', 1, 2 ) finds the second one (both starting the search from the first character of str). Now, to retrieve the first value you need, try using REGEXP_SUBSTR( str, '[0-9]+', INSTR( str, ':', 1, 1 ), 1 ) and for the second one try REGEXP_SUBSTR( str, '[0-9]+', INSTR( str, ':', 1, 2 ), 1 ).
TBH, I never used Oracle SQL, and all above is based on the Oracle page you linked and my experience with regular expressions. Anyway... hope that helps. --CiaPan (talk) 21:44, 13 November 2019 (UTC)

@CiaPan: Wow, thank you for the detailed response! I did a few samples this morning and it's working fine. Much obliged! I find regex to be a bit similar to SQL in that they're very useful, very finicky, and very unhelpful when you get them wrong... Matt Deres (talk) 14:15, 14 November 2019 (UTC)

@Matt Deres: Thank you for your response, it's nice to know my answer was helpful to you. Regular expressions are not very difficult to compose, but (except those very short and simple) they are quite hard to read (and debug). The more because different environments implement different syntax (e.g. they use comply with POSIX definitions of not, they handle shorthands or not, etc.). Best regards, and good luck in exploring the new area of programming! --CiaPan (talk) 14:35, 14 November 2019 (UTC)