• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

basic sql statement doubt

 
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using basic sql statement to display one column of a table as below:



which fetches the column num from test_config table.

The data are as below:



Now I want to modify my query so that I want to display only as below depending upon num*

Suppose for num1, the list will as below:
num1_01
num1_02

for num2:
num2_01
num2_02
num2_03

How do I modify my query so that num1-* can be fetched in a list. Please let me know.
 
Ranch Hand
Posts: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Seems like you need to use Regular Expression (RE) in your SELECT query.
Look for the LIKE keyword in SQL syntax, for start have this link OR consult Google for specific RE matching in SQL.
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Gopu Akraju wrote:I am using basic sql statement to display one column of a table as below:



which fetches the column num from test_config table.

The data are as below:



Now I want to modify my query so that I want to display only as below depending upon num*

Suppose for num1, the list will as below:
num1_01
num1_02

for num2:
num2_01
num2_02
num2_03

How do I modify my query so that num1-* can be fetched in a list. Please let me know.



use the substring function

select num from test_config where subst(num,1,4) = 'num1'
 
Gopu Akraju
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Paul and Sagar.
 
Gopu Akraju
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have another set of column which is bit tricky for me to formulate the sql statements. Hence kindly help.

The column data:



For example num6-HI-R0023 can be constructed using 2 variables from the user and the middle part can be any 2 letters but all the columns are unique. And the string is always 13 characters length.

I have a mechanism to get the first and the last part of the string from the user in 2 variables. For example variable1 = num6 and variable2 = 23. Variable 2 can be either 1 or 2 or 3 digit as 1 or 23 or 230.

How to construct my sql statement such as it is equivalent to "select * from test_config where num = num6-HI-R0023" Please let me know as this is bit tricky for me.

 
Bartender
Posts: 4116
72
Mac TypeScript Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Gopu Akraju wrote:

I have a mechanism to get the first and the last part of the string from the user in 2 variables. For example variable1 = num6 and variable2 = 23. Variable 2 can be either 1 or 2 or 3 digit as 1 or 23 or 230.

How to construct my sql statement such as it is equivalent to "select * from test_config where num = num6-HI-R0023" Please let me know as this is bit tricky for me.



Since you have the values in variables you have to put them in the SQL statement as below

"select * from test_config where num = "+ Variable1+"-"+Variable2....



You may change the format to suit your requirement.
 
Sagar Rohankar
Ranch Hand
Posts: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Gopu Akraju wrote:

How to construct my sql statement such as it is equivalent to "select * from test_config where num = num6-HI-R0023" Please let me know as this is bit tricky for me.



How good you're in Regular Expression (RE), As I suggest above, Have you tried to use 'LIKE' keyword in WHERE clause..

I ll help you in creating an RE string first,

RE = "num6-" (U have it )
+
[a-zA-Z](2) (read, as any alphbet, 2 times)
+
"-ROO" (I hope this "ROO" is common)
+
"23" (U have it )

Once you have RE, put that in 'LIKE' clause (Use the link I provided for 'LIKE' syntax).

Sorry, for not providing an answer directly, I want you you to try and enjoy it !!
 
Gopu Akraju
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Sagar, I will definitely try.
Cheers
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Gopu Akraju wrote:I have another set of column which is bit tricky for me to formulate the sql statements. Hence kindly help.

The column data:



For example num6-HI-R0023 can be constructed using 2 variables from the user and the middle part can be any 2 letters but all the columns are unique. And the string is always 13 characters length.

I have a mechanism to get the first and the last part of the string from the user in 2 variables. For example variable1 = num6 and variable2 = 23. Variable 2 can be either 1 or 2 or 3 digit as 1 or 23 or 230.

How to construct my sql statement such as it is equivalent to "select * from test_config where num = num6-HI-R0023" Please let me know as this is bit tricky for me.



you use concatenation in your where clause
in oralce it would be
where num = variable1||'HI'||variable2

in mysql
where num = CONCAT(variable1, 'HI', variable2)
 
It will give me the powers of the gods. Not bad for a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic