Strings and variables in select statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Strings and variables in select statements

Author

Topic

bwreath
Starting Member

2 Posts

Posted - 2004-03-23 : 16:39:14

Hi,

I would appreciate help with the following.
I have a table where each of the values in a column consists of a
combination of letters and numbers followed by a decimal then a single digit.
Examples of the two types of entries that could be found in this column(accession_num)
are:
BQ877252.1
AA3588976.2
and possibly
T84780
The entries above are found in a table called hs_identifiers.
The hs_identifiers table has two columns accession_num and hs_id_num.

Given an accession_num I want to be able to look up in the hs_identifiers table to look up for the corresponding hs_id_num.

This would be a simple select statement for example
Select hs_identifiers.hs_id_num from hs_identifiers,
where (accession_num = (result of another select statement)._)
or (accession_num=(result of another select statement));

The only thing is I am not sure what the syntax is for
the part (accession_num = (result of another select statement)._)

In the section (result of another select statement)._) I am trying to
say the result concatenated with a period concatenated with one wild card character.
For example: result of another select statement = BQ877252
period is '.'
and wild card character 1,2,3, etc.
The whole string concatenated together would be BQ877252.1

A second question that I have is the section of (result of another
select statement) could be more than one accession_num. An entry in a
column has more than accession_num and they are separated by a space.

I would like to be able to know how to handle it in two different ways.
One way is if the (result of another select statement) yields more than one accession_num I would like to know what each hs_id_num is.

I would also like to know how to if (result of another select statement) yields more than one accession_num to ignore that case
and go on to look at another (result of another select statement)
in a loop.

At the core of both of these options I would like to know how to detect that there is more than one accession_num

thanks!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-23 : 23:00:01

Wow!

Thats a whole lot of things you need to take care of.

For the wild card character stuff you can take a look at "LIKE" in Books OnLine - this has good ways of using wild cards etc.

For the rest I think it would be easier to get some help if you posted example data and desired results.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources