Translating with DrWatson… this can take a few seconds the first time.
This is a genuine, complex translation. DrWatson protects commands, error codes, and log output while naturally translating the surrounding text. It’s translated once and saved.
Bernd Steinhauer — — source: Informix-list mailing list archive (1991-1998)
Hello,
I need a query that selects all numeric ids that start with the same number.
For example I want to select all rows where emp_no starts with 12, but
emp_no is a integer field.
emp_no
-------
12
123
120003
123434434
Any help would be appreciated !
Thanks in advance
--
********************************************************
DELICom
DPD Deutscher Paket Dienst GmbH & Co. KG
Wailandtstrasse 1, D-63741 Aschaffenburg
Tel.: +49 (0)6021/492-6064
Fax: +49 (0)6021/492-6503
E-Mail: Bernd.Steinhauer@dpd.de
Internet:www.dpd.de
********************************************************
↪ replying to Bernd Steinhauer
Christophe MAUMONT — — source: Informix-list mailing list archive (1991-1998)
Bernd Steinhauer <bernd.steinhauer@dpd.de> a 'crit dans le message :
9692fn$k86d8$1@ID-59884.news.dfncis.de...
> Hello,
>
> I need a query that selects all numeric ids that start with the same
number.
> For example I want to select all rows where emp_no starts with 12, but
> emp_no is a integer field.
>
> emp_no
> -------
> 12
> 123
> 120003
> 123434434
>
> Any help would be appreciated !
>
WITH A TEMP TABLE LIKE :
CREATE TEMP TABLE tmp_table (emp_no_in_char CHAR(12));
INSERT INTO tmp_table SELECT emp_no FROM your_table;
SELECT * FROM tmp_table WHERE emp_no_in_char MATCHES "12*";
Good luck.
> Thanks in advance
>
> --
> ********************************************************
> DELICom
> DPD Deutscher Paket Dienst GmbH & Co. KG
> Wailandtstrasse 1, D-63741 Aschaffenburg
> Tel.: +49 (0)6021/492-6064
> Fax: +49 (0)6021/492-6503
> E-Mail: Bernd.Steinhauer@dpd.de
> Internet:www.dpd.de
> ********************************************************
>
>
↪ replying to Bernd Steinhauer
Andrew Hamm — — source: Informix-list mailing list archive (1991-1998)
Bernd Steinhauer wrote in message
<9692fn$k86d8$1@ID-59884.news.dfncis.de>...
>
>I need a query that selects all numeric ids that start with the same
number.
>For example I want to select all rows where emp_no starts with 12, but
>emp_no is a integer field.
>
That sounds like a really wierd programming requirement, but then I think
waking up in the morning is really wierd.
Try this:
where (emp_no || " ") matches "12*"
Unfortunately, the conversion to string prior to the concatenation operator
may put leading blanks which will mess up the matches operator.
You could also try writing a simple stored procedure to perform the
concatentation and trim the leading blanks.
In either case, kiss goodbye to the possibility to use indexes. Performance
for this requirement could be appalling. I'd be consider denormalising
slightly (jeers from the crowd) by adding a column containing the magic
first two digits if this is a common type of query. You may be wise to add a
contraint tying the two columns together by a rule to ward off evil data
inconsistency.
We use strictly necessary cookies to make this site work. With your
consent we’d also use optional cookies for analytics and marketing. You can accept all,
reject all, or choose. Read our Cookie Policy.