[FIXED] How to isolate content until the first double newline sequence?

Issue

<ul>
    <li><a href="#">Foo</a></li>
    <li><a href="#">Foo</a></li>
    <li><a href="#">Foo</a></li>
</ul>

<ul>
    <li><a href="#">Bar</a></li>
    <li><a href="#">Bar</a></li>
    <li><a href="#">Bar</a></li>
</ul>

How can I get any content until the first blank line?

NOTE: First and second part of the content doesn't always start with ul.

Solution

preg_match('/\A.*?(?=\s*^\s*$)/smx', $subject, $regs);
$result = $regs[0];

Explanation

preg_match(
    '/\A    # Start of string
    .*?     # Match any number of characters (as few as possible)
    (?=     # until it is possible to match...
     \s*    #  trailing whitespace, including a linebreak 
     ^      #  Start of line
     \s*    #  optional whitespace
     $      #  End of line
    )       # (End of lookahead assertion)/smx', 
    $subject, $regs);
$result = $regs[0];

assuming that you count lines that contain nothing but whitespace as blank lines. If not, remove the "optional whitespace" line.

Answered By - Tim Pietzcker

Answer Checked By - Cary Denson (PHPFixing Admin)

Read More

[FIXED] How I can use Substring for Update in SQL right?

Issue

once again I have a problem in SQL. I want to use a substring, which I otherwise used for a SELECT query, for a table UPDATE. The query looks like this:

SELECT SUBSTRING(h.action,LOCATE('"',h.action)+1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action))) AS File, 
       h.TIMESTAMP, 
       h.user, 
       d.uid, 
       d.size 
       d.id 
from history h 
INNER JOIN data d ON h.contract = d.contract 
LEFT JOIN history ON d.user = history.user 
WHERE ( SELECT DISTINCT SUBSTRING(h.action,LOCATE('"',h.action)+1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action)))) IN (SELECT filename FROM data) 
and h.contract=xy AND h.action LIKE 'file%added' GROUP BY File

Now I want to update the table data. The column 'user' and the column 'Timestamp' are to be transferred from the table history to data. My last attempt for the update command was the following:

update data d
set 
d.user = v.user,
d.upload= h.`timestamp` 
inner join
history h on d.contract = h.contract 
where 
d.filename in (SELECT DISTINCT SUBSTRING(h.action,LOCATE('"',h.action)+1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action)))) and h.action like 'File%added'

Either I received a syntax error so far or it was a SQL command which returned 0 rows to modify. I hope that is an easy problem to fix.

Thank you in advance for your help!

Solution

I don't know if below query would help, because as I mentioned in the comment you should provide data examples and expected results.

The correct UPDATE syntax is:

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference [PARTITION (partition_list)] [FOR PORTION OF period FROM expr1 TO expr2] SET col1={expr1|DEFAULT} [,col2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ... [WHERE where_condition]

So your update query should be:

update data d
inner join history h on d.contract = h.contract 
set  d.user = v.user, d.upload= h.`timestamp` 
where d.filename in (SELECT DISTINCT SUBSTRING(h.action,LOCATE('"',h.action)+1,(((LENGTH(h.action))-LOCATE('"', REVERSE(h.action))-1)-LOCATE('"',h.action)))) 
and h.action like 'File%added' ;

Answered By - Ergest Basha

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More

[FIXED] How can I use multiple substring functions in an update statement?

Issue

I have a table with a column called candidate_name in which the names are formatted as lastname, firstname. I'd like to update the column so that the names are in firstname lastname format.

I wrote this query to see if the string functions were working correctly:

SELECT 
    SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1) AS last,
    SUBSTRING(candidate_name, LOCATE(',', candidate_name) + 2, LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1) AS first
FROM 
    colorado_project.candidates
WHERE 
    candidate_name = 'AHREND, JARED';

This returns the strings into new columns as expected:

But I don't know how to use these in an update statement. I've tried many statements similar to this:

UPDATE candidates
SET candidate_name = SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1),
                     SUBSTRING(candidate_name, LOCATE(',', candidate_name) + 2,
        LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1)
WHERE candidate_name = 'AHREND, JARRED';

Pretty sure I've gotten every error known to mankind at this point, but the main error I get is

'1292 Truncated incorrect DOUBLE value 'AHREND'.

I've searched around and it seems like this error is often associated with comparing a string value to a number value, but I don't understand by the substring functions would be returning a number value now, when they were returning strings before? Or maybe that's not whats going on here. Any help would be appreciated. Thanks!

Solution

You must concatenate the 2 parts of the name:

UPDATE candidates
SET candidate_name = CONCAT( 
  SUBSTRING(candidate_name, LOCATE(',', candidate_name) + 2, LENGTH(candidate_name) - LOCATE(',', candidate_name) - 1),
  ' ',
  SUBSTRING(candidate_name, 1, LOCATE(',', candidate_name) - 1)
)
WHERE candidate_name = 'AHREND, JARED';

See the demo.

or, simpler with SUBSTRING_INDEX():

UPDATE candidates
SET candidate_name = CONCAT( 
  TRIM(SUBSTRING_INDEX(candidate_name, ',', -1)),
  ' ',
  SUBSTRING_INDEX(candidate_name, ',', 1)
)
WHERE candidate_name = 'AHREND, JARED';

See the demo.

Answered By - forpas

Answer Checked By - Senaida (PHPFixing Volunteer)

Read More

[FIXED] How to Select a substring in Oracle SQL up to a specific character?

Issue

Say I have a table column that has results like:

ABC_blahblahblah
DEFGH_moreblahblahblah
IJKLMNOP_moremoremoremore

I would like to be able to write a query that selects this column from said table, but only returns the substring up to the Underscore (_) character. For example:

ABC
DEFGH
IJKLMNOP

The SUBSTRING function doesn't seem to be up to the task because it is position-based and the position of the underscore varies.

I thought about the TRIM function (the RTRIM function specifically):

SELECT RTRIM('listofchars' FROM somecolumn) 
FROM sometable

But I'm not sure how I'd get this to work since it only seems to remove a certain list/set of characters and I'm really only after the characters leading up to the Underscore character.

Solution

Using a combination of SUBSTR, INSTR, and NVL (for strings without an underscore) will return what you want:

SELECT NVL(SUBSTR('ABC_blah', 0, INSTR('ABC_blah', '_')-1), 'ABC_blah') AS output
  FROM DUAL

Result:

output
------
ABC

Use:

SELECT NVL(SUBSTR(t.column, 0, INSTR(t.column, '_')-1), t.column) AS output
  FROM YOUR_TABLE t

Reference:

• SUBSTR
• INSTR

Addendum

If using Oracle10g+, you can use regex via REGEXP_SUBSTR.

Answered By - OMG Ponies

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] How to split delimited string with leading and trailing characters to array?

Issue

For example, if I have this string:

$stuff = "[1379082600-1379082720],[1379082480-1379082480],[1379514420-1379515800],";

I know can do this to split it into an array like this:

$stuff = str_replace(array("[","]"),array("",""),$stuff);
$stuff = explode(",",$stuff);

But it seems like there would be an easier way since the string is already in an array form almost. Is there an easier way?

Solution

You can get inside [] with preg_match_all. Try following:

preg_match_all("/\[(.*?)\]/",$stuff, $matches);

Output of $matches[1]

array (size=3)
  0 => string '1379082600-1379082720' (length=21)
  1 => string '1379082480-1379082480' (length=21)
  2 => string '1379514420-1379515800' (length=21)

Answered By - Bora

Answer Checked By - Gilberto Lyons (PHPFixing Admin)

Read More

[FIXED] How to substring a text easily

Issue

Alright here the question. This is an example text

Nokia 700<span>Stok Kodu: 389952</span>
<br><span style="background-image:none; margin:0; padding:0; font:14px/18px Arial,Helvetica,sans-serif">Nokia 700 Cep Telefonu PDA, Dokunmatik, Bluetooth, Radyo</span>

I want to get "Nokia 700" which starts with index 0 and ends with <span> and i am able to do it with the way below

var singleNode = myDoc.DocumentNode.SelectSingleNode(srxProductName);
string srProductName = singleNode.InnerHtml.ToString()
    .Substring(0, singleNode.InnerHtml.ToString().IndexOf("<span>"));

The question is i wonder are there any easier or more practical way of doing it.

Solution

If you already have the node containing your text in singleNode, you can just extract the first child node, which should be a text node, and get its value:

var productName = singleNode.ChildNodes[0].Value;

Answered By - Jacob

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] Reverse the direction of all sequences of letters in a string

Issue

Is it possible to reverse only the letters in a string?

For instance if I have the word $word = "word,!";

I need the result to be $result = "drow,!";

I tried using strrev, but it reverses the punctuation marks too.

Solution

 $word = "word,!pineapple--pizza";
 $revd = preg_replace_callback('#([A-Za-z]+)#', 'rev_first', $word);

function rev_first($matches){
    return strrev($matches[1]);
}

http://3v4l.org/PXEs8

Is this what you're looking for?

Answered By - scragar

Read More

[FIXED] Reverse the order of letters in the last word in a string

Issue

I am trying to reverse the final word in a string.

A multi-word string:

$string = "i like to eat apple";
// to be:  i like to eat elppa

A single word string:

$string = "orange";`
//to be:   egnaro

How can I reverse the order of letters in only the last word -- regardless of how many words are in the string?

Solution

I have been Found the answer. For my own question

$split = explode(" ", $words);
$v_last = $split[count($split)-1]; 
$reverse = strrev($v_last);
$f_word = chop($words,$v_last);
echo $f_word;
echo $reverse;

Answered By - brinardi

Read More