Magento 2 – How to Get URL Data by Request Path

If you want exclude category url from product url for new product you need enable setting from admin>system>catalog>Catalog>Search engine Optimization> Use Categories Path for Product URLsmake itnoalsoCreate Permanent Redirect for URLs if URL Key Changedityes`

Here some other trick

http://www.doingthing.com/blog/item/1164
http://www.mikesmullin.com/development/use-categories-product-urls-magento-seo-without-duplicate-content/

Here the another process of This task...

Step1: create a backup core_url_rewrite table with table data and it structure.

Step2: create another duplicate table of core_url_rewrite using my query below:

CREATE  TABLE  `core_url_rewrite_dump` (  `url_rewrite_id` int( 10  )  unsigned NOT  NULL  AUTO_INCREMENT  COMMENT  'Rewrite Id',
 `store_id` smallint( 5  )  unsigned NOT  NULL DEFAULT  '0' COMMENT  'Store Id',
 `id_path` varchar( 255  )  DEFAULT NULL  COMMENT  'Id Path',
 `request_path` varchar( 255  )  DEFAULT NULL  COMMENT  'Request Path',
 `target_path` varchar( 255  )  DEFAULT NULL  COMMENT  'Target Path',
 `is_system` smallint( 5  ) unsigned DEFAULT  '1' COMMENT  'Defines is Rewrite System',
 `options` varchar( 255  )  DEFAULT NULL  COMMENT  'Options',
 `description` varchar( 255  )  DEFAULT NULL  COMMENT  'Deascription',
 `category_id` int( 10  ) unsigned  DEFAULT NULL  COMMENT  'Category Id',
 `product_id` int( 10  ) unsigned  DEFAULT NULL  COMMENT  'Product Id',
 PRIMARY  KEY (  `url_rewrite_id`  ) ,
 UNIQUE  KEY  `UNQ_CORE_URL_REWRITE_REQUEST_PATH_STORE_ID` (  `request_path` ,  `store_id`  ) ,
 UNIQUE  KEY  `UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID` (  `id_path` ,  `is_system` ,  `store_id`  ) ,
 KEY  `IDX_CORE_URL_REWRITE_TARGET_PATH_STORE_ID` (  `target_path` ,  `store_id`  ) ,
 KEY  `IDX_CORE_URL_REWRITE_ID_PATH` (  `id_path`  ) ,
 KEY  `IDX_CORE_URL_REWRITE_STORE_ID` (  `store_id`  ) ,
 KEY  `FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` (  `category_id`  ) ,
 KEY  `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID` (  `product_id`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = utf8 COMMENT  =  'Url Rewrites';

SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

INSERT INTO `core_url_rewrite_dump` SELECT * FROM `magento1702v1`.`core_url_rewrite

Step4:

final run the below code

UPDATE `magento1702v1`.`core_url_rewrite` as p  SET `target_path`=(SELECT rurl.request_path
FROM `core_url_rewrite_dump` as rurl
WHERE rurl.product_id IS NOT NULL
AND rurl.category_id IS NULL 
AND rurl.product_id=p.product_id
LIMIT 0 , 1)  
,`options` = 'RP' 
WHERE p. product_id IS NOT NULL
AND p.category_id  IS NOT NULL 

This is how it works. The setting “Use Categories Path for Product URLs” to "NO" will work as expected on every page(Shopping cart, Recently viewed, Crosssell/upsell, wishlist etc) except product listing page.