Underscore Wildcard

Underscore Wildcard

Post by Simo » Fri, 27 Feb 2004 01:57:28



I have a number generation script which queries for the max value of a
pattern using:- sSql = "select max (CN_DOCUMENT_NUMBER) MaxNr from
tn_Documentation where CN_DOCUMENT_NUMBER Like '" & sPrefix & "%' "

Typical Documen Number values are OSI_PROG_001, OSI_PROG_002, OSI_PR_001 etc

I then increment the final numeric by 1. When creating a new record where
the query knows that sPrefix = "OSI_PROG_" and that for another new record
sPrefix = "OSI_PR_" (note the underscore at the end is included in sPrefix,
it still won't allow a second PR to be made as the query finds "PROG"
values.

I understand that this is due to the underscore character being the single
digit wildcard for SQL syntax.

Any ideas how to resolve this?

 
 
 

Underscore Wildcard

Post by Delbert Glas » Fri, 27 Feb 2004 02:05:21


sPrefix = "OSI_PR_"
or
sPrefix = "OSI_PROG_"
then
LIKE sPrefix + '[0-9][0-9][0-9]'

Bye,
Delbert Glass


Quote:> I have a number generation script which queries for the max value of a
> pattern using:- sSql = "select max (CN_DOCUMENT_NUMBER) MaxNr from
> tn_Documentation where CN_DOCUMENT_NUMBER Like '" & sPrefix & "%' "

> Typical Documen Number values are OSI_PROG_001, OSI_PROG_002, OSI_PR_001
etc

> I then increment the final numeric by 1. When creating a new record where
> the query knows that sPrefix = "OSI_PROG_" and that for another new record
> sPrefix = "OSI_PR_" (note the underscore at the end is included in
sPrefix,
> it still won't allow a second PR to be made as the query finds "PROG"
> values.

> I understand that this is due to the underscore character being the single
> digit wildcard for SQL syntax.

> Any ideas how to resolve this?


 
 
 

Underscore Wildcard

Post by Delbert Glas » Fri, 27 Feb 2004 02:10:30



or

sPrefix = "OSI[_]PR[_]"
and
sPrefix = "OSI[_]PROG[_]"

Bye,
Delbert Glass


> sPrefix = "OSI_PR_"
> or
> sPrefix = "OSI_PROG_"
> then
> LIKE sPrefix + '[0-9][0-9][0-9]'

> Bye,
> Delbert Glass



> > I have a number generation script which queries for the max value of a
> > pattern using:- sSql = "select max (CN_DOCUMENT_NUMBER) MaxNr from
> > tn_Documentation where CN_DOCUMENT_NUMBER Like '" & sPrefix & "%' "

> > Typical Documen Number values are OSI_PROG_001, OSI_PROG_002, OSI_PR_001
> etc

> > I then increment the final numeric by 1. When creating a new record
where
> > the query knows that sPrefix = "OSI_PROG_" and that for another new
record
> > sPrefix = "OSI_PR_" (note the underscore at the end is included in
> sPrefix,
> > it still won't allow a second PR to be made as the query finds "PROG"
> > values.

> > I understand that this is due to the underscore character being the
single
> > digit wildcard for SQL syntax.

> > Any ideas how to resolve this?

 
 
 

Underscore Wildcard

Post by Dan Blak » Fri, 27 Feb 2004 20:21:06


Hi Simon,

I have found the best way of working around the problem of searching names with MS Reserved properties in the like clause is to replace them with an alternative character.

An example:
drop procedure spr_objectphrasesearch
go
create procedure spr_objectphrasesearch

/************************************************************************************************************************/
/* V1 : DB : Purpose: Allow searching of procedural text tables, for phrases or missing key-variables                   */
/*              This can then be isolated to generate process improvement, such as replacement of cursors               */
/*              This code, is also useful to check for any drop trigger or drop table statements, whilst allowing       */
/*              To check the section of code affected                                                                   */
/*              This can be used to QA, multiple sections of a database application                                     */
/*                                                                              */
/* V2 : DB :    This will allow searching for calling of system generated calls to sp_ or xp_                           */
/*              There is a section allowing the _ code to also be searched upon within sql server                       */
/*              This demonstrates a limitation of searching for the _ character within a like clause, and the           */
/*              Mechanism to work around this limiatation.                                                              */
/*              To allow searching for a specific phrase such as sp" where " indicates any particular character within  */
/*              the search.                                                                                             */
/*              The reason for this particular implementation is to ensure that the normal searchs for sp_ or xp_       */
/*              is the default behaviour.  " is also not used as a single character                                        */
/*              This is to allow basic pattern-searching                                                                */
/*              The rest of functionality within the like clause is unaffected                                          */
/*              This limits " to not occuring anywhere within the phrase                                           */
/*                                                                                                                      */
/*                                                                                                                      */
/*      Testing completed: 04 Feb 2004                                                                                  */
/*      Demo execution: (Build on master database, to allow access from mutliple databases                              */
/*      spr_objectphrasesearch  'sp"_'  Returns all objects text/name that contains sp<any_single_character>_                */
/************************************************************************************************************************/


as


select SC.text as object_text,
        SO.name as object_name,
        SO.xtype
from syscomments SC
join sysobjects SO
on
--SO.xtype = 'P' and
 SC.id=object_id(So.name)


order by SC.id,SC.number,SC.colid
OPTION (ROBUST PLAN)

Regards,

Dan.

 
 
 

1. Escaping an underscore wildcard?

I have production tables and work tables in the same tablespace.
Each worktable is given a letter prefix followed by an underscore
to distinguish it from the production tables, and to allow all
worktables with a common prefix to be identified. We use Oracle v7.1.

I would like to be able to do the following:
   Select tname from tab where tname like 'A_%';

The problem is that the underscore is a single character
wildcard. So in addition to returning tablenames like
A_APPLE, I also get APPLE.

I thought that I might be able to use the backslash as an escape like
this:
   Set escape on
   Select tname from tab where tname like 'A\_%';
but the backslash is ignored and the underscore is still used as a
wildcard. It seems that the escape only works to escape variable
substitution not wildcard substitution.

I would love to be able to temporarily change the wildcard character
to something other than and underscore, but this doesn't seem
possible.

Any suggestions. Thanks.

--

2. .NULL. ?

3. Using like comparisions on string data with underscores

4. Foxpro 2.0 to 2.5 Upgrade

5. NT problems: names with underscores?

6. What's the error message meaning?

7. Underscore in field name

8. VFP 5: Mixing SQL and xBASE Table Commands

9. Doubled underscore symbols in field names when SELECT from SQL 6.5

10. Replacing underscores???

11. SQLTables fails when database name include underscore.

12. Fields with Underscore

13. Replace underscore behavior?