Unable to parse a string

Database
Enthusiast

Unable to parse a string

Hi ,

I have huge URL's list and need to check for some particular code in the URL .

ex: i have a URL http ://services.com/eShop/login.jsp

i need to extract the eShop code from this url

sometimes we may get URL as https://www.services.com/eServices/eshop/......

then i need to check for eShop .

if i consider the first URL to form a query using Substring then it will not be useful for 2nd URL .

any suggestion how can we write a generic function to solve this .
6 REPLIES
Senior Apprentice

Re: Unable to parse a string

You need the remaining string after the eShop?

SUBSTRING(url FROM POSITION('/eShop/' IN url) + 7)

Dieter
Enthusiast

Re: Unable to parse a string

no i dont want remaining string . This function shud work for both http and https cases .

more over if the eshop comes like this then

https://www.services.com/eServices/.../eshop/......
Senior Apprentice

Re: Unable to parse a string

You don't want the remaining string, ok.

But what do you actually want?
I don't get it.

Is eShop ment to be a placeholder and you want to extract the actual value in the position of eShop?
Then you might use a CASE for the different possibilities.

Dieter
Enthusiast

Re: Unable to parse a string

the URL strings may vary for different application wise .some time the eShop will be there in second position

https://www.services.com/eServices/eshop/......

or sometimes https://www.services.com/eServices/..../eshop/......
or
http ://services.com/eShop/login.jsp
in all thse cases i want to find a substring eShop
pls suggest for all those
Senior Apprentice

Re: Unable to parse a string

How to decide which one to use:
https://www.services.com/eServices/eshop/....
or
https://www.services.com/eServices/..../eshop/....

I don't think there's an easy way to do that, unless you know the name of the eShop in advance.
So if the number of shops is limited and known you could use
case
when urlm like '%/eShop1/' then eShop1
when urlm like '%/eShop2/' then eShop2
etc.

Dieter

Re: Unable to parse a string

Hi There,

is there a way to convert ASCII to UTF-8 (varchar to URL encode) or for example:

space to  %20
! to %21
" to %22
# to %23

so 'my test' will become 'my%20test'; i know it can be done with CASE but I would like to know if there is a proper way of converting/translating charcters to UTF-8

Thanks