Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

basic sql statement doubt

 
Gopu Akraju
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
  • 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.
 
Sagar Rohankar
Ranch Hand
Posts: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • 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.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
Thanks Paul and Sagar.
 
Gopu Akraju
Ranch Hand
Posts: 242
  • Mark post as helpful
  • send pies
  • 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.

 
Vijitha Kumara
Bartender
Posts: 3913
9
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • 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: 2907
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
Thanks Sagar, I will definitely try.
Cheers
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • 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)
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic