The moose likes JDBC and the fly likes basic sql statement doubt Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "basic sql statement doubt" Watch "basic sql statement doubt" New topic
Author

basic sql statement doubt

Gopu Akraju
Ranch Hand

Joined: Jan 13, 2008
Posts: 242
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

Joined: Feb 19, 2008
Posts: 2902
    
    1


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.


[LEARNING bLOG] | [Freelance Web Designer] | [and "Rohan" is part of my surname]
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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

Joined: Jan 13, 2008
Posts: 242
Thanks Paul and Sagar.
Gopu Akraju
Ranch Hand

Joined: Jan 13, 2008
Posts: 242
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

Joined: Mar 24, 2008
Posts: 3855

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.


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

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

Joined: Jan 13, 2008
Posts: 242
Thanks Sagar, I will definitely try.
Cheers
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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)
 
 
subject: basic sql statement doubt