Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DELIMITER $$
- CREATE FUNCTION SPLIT_STR(
- x VARCHAR(255),
- delim VARCHAR(12),
- pos INT
- )
- RETURNS VARCHAR(255) DETERMINISTIC
- BEGIN
- RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
- LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
- delim, '');
- END$$
- DELIMITER ;
- SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
- SPLIT_STR(membername, ' ', 2) as memberlast
- FROM users;
- SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
- SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
- FROM users;
- SELECT IF(
- LOCATE(' ', `membername`) > 0,
- SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
- `membername`
- ) AS memberfirst,
- IF(
- LOCATE(' ', `membername`) > 0,
- SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
- NULL
- ) AS memberlast
- FROM `user`;
- UPDATE `user` SET
- `memberfirst` = IF(
- LOCATE(' ', `membername`) > 0,
- SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
- `membername`
- ),
- `memberlast` = IF(
- LOCATE(' ', `membername`) > 0,
- SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
- NULL
- );
- SELECT
- SUBSTRING_INDEX(`membername`, ' ', 1) AS `memberfirst`,
- SUBSTRING_INDEX(`membername`, ' ', -1) AS `memberlast`
- ;
- SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', 2 ),' ',1) AS b,
- SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', -1 ),' ',2) AS c FROM `users` WHERE `userid`='1'
- UPDATE people_exit SET last_name = SUBSTRING_INDEX(fullname,' ',-1)
- UPDATE people_exit SET middle_name = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fullname,last_name,1),' ',-2))
- UPDATE people_exit SET middle_name = '' WHERE CHAR_LENGTH(middle_name)>3
- UPDATE people_exit SET first_name = SUBSTRING_INDEX(fullname,concat(middle_name,' ',last_name),1)
- UPDATE people_exit SET first_name = middle_name WHERE first_name = ''
- UPDATE people_exit SET middle_name = '' WHERE first_name = middle_name
- SELECT Substring(nameandsurname, 1, Locate(' ', nameandsurname) - 1) AS
- firstname,
- Substring(nameandsurname, Locate(' ', nameandsurname) + 1) AS lastname
- FROM emp
- UPDATE tblAuthorList SET AuthorFirst = SUBSTRING_INDEX(AuthorLast,',',-1) , AuthorLast = SUBSTRING_INDEX(AuthorLast,',',1);
- SELECT TRIM(
- IF(
- LOCATE(' ', `name`) > 0,
- LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
- `name`
- )
- ) AS first_name,
- TRIM(
- IF(
- LOCATE(' ', `name`) > 0,
- SUBSTRING_INDEX(`name`, ' ', -1) ,
- NULL
- )
- ) AS last_name
- FROM `users`;
- UPDATE `users` SET
- `first_name` = TRIM(
- IF(
- LOCATE(' ', `name`) > 0,
- LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
- `name`
- )
- ),
- `last_name` = TRIM(
- IF(
- LOCATE(' ', `name`) > 0,
- SUBSTRING_INDEX(`name`, ' ', -1) ,
- NULL
- )
- );
- set last_name=trim(SUBSTRING_INDEX(first_name, ' ', -1)), first_name=trim(SUBSTRING(first_name,1,length(first_name) - length(SUBSTRING_INDEX(first_name, ' ', -1)))) where list_id='$List_ID' and length(first_name)>0 and length(trim(last_name))=0
- UPDATE `salary_generation_tbl` SET
- `modified_by` = IF(
- LOCATE('$', `other_salary_string`) > 0,
- SUBSTRING(`other_salary_string`, 1, LOCATE('$', `other_salary_string`) - 1),
- `other_salary_string`
- ),
- `other_salary` = IF(
- LOCATE('$', `other_salary_string`) > 0,
- SUBSTRING(`other_salary_string`, LOCATE('$', `other_salary_string`) + 1),
- NULL
- );
- SPLIT_STR(<column>, '<delimiter>', <index>)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement