• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to get a substring value in table column filed

 
hitendra sunkara
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a table named collateral.In that table i have a column named URL

In that column we will be having urls like this:
ajaxItemDisplay?catlog_id=1234&storeId=12207
ajaxItemDisplay?catlog_id=122324&storeId=12207
ajaxItemDisplay?catlog_id=12332324&storeId=12207

How can i get the cataloigId value from the above url.Can we use SUBSTRING
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hitendra sunkara wrote:How can i get the cataloigId value from the above url.Can we use SUBSTRING


Sure you can use substring to the value. But the real issue the length since the id is not fixed length!

Are you plan to do the substring in sql or in your program (eg java)?
 
sreenu valireddi
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if your storeid is same for all. then you can use like this

String id = str.replaceAll("[\\D]", "");
String cataloigId = id.substring(0,id.length()-5 );
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Add a column for the catalog ID.
Extract the catalog ID from the URL (using substring or regex) and store it in the new column when you save the record to the table.
Now you can query for the catalog ID easily and efficiently, without having to extract it from the URL every time.
In general, you should save significant data values (such as catalog ID or store ID) individually instead of having to apply extra processing to extract them from composite values every time you query them.
 
hitendra sunkara
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Chris,

Actually that is OOB feature.We canot do it now.and moreover it canot be in the same location.some times it can be like this:
ajaxOrderItem>storeId=12709&partNum=345435&catalogId=2345

Iam actaully migrating E-spots.There will be number of url's like this in that table.Now i need to replace all the old catalogId's with new ones.
 
hitendra sunkara
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi K. Tsang

I need an sql.As length is not fixed. catalogid can be at any pos of the url some times it may be frst parameter and some time it may be the lastparamenetr
 
sreenu valireddi
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
split the string with '&'

String as[] = yourUrl.split("&");
for(String s : as)
{
if(s.contains("catlog_id"))
System.out.println("catalog_id ------------------"+ s.replaceAll("[\\D]", ""));
}


no matter where catlog_id is.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic