?
Solved

concatenation and selecting alpha range in SQL

Posted on 2004-04-05
8
Medium Priority
?
594 Views
Last Modified: 2006-11-17
Hi,

I am programming in visual basic and am having trouble with a couple of queries. They must work against access and oracle. The problem is, as I am working off site I only have access and do not know if what I am doing will work against oracle as well.

Problem 1 - concatenation.

I need to select from 2 separate db fields and concatenate them as one field in the query results. So I want surname and initials to be selected as name.

This works in Access:
select [surnamefield] + ' ' + [initialsfield] as name from....

will this work in oracle as well ?

Problem 2 - select alpha range.

The customer's office is split by surname of their customers. So miss smith deals with all customers whose surname is in range beginning A to BL. If she needs to print all pertinent items for that day for her whole alpha range the sql to do this in access is:

select * from [table] where [surnamefield] >= 'A%' and [surnamefield] <= 'BL%'

again would this work in oracle ?

thanks for your help
0
Comment
Question by:cookster26
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10756390
in ORACLE, the concatenation operator is ||, not + so in ORACLE, the query would look like

select [surnamefield] || ' ' || [initialsfield] as name from....


Since yoou appear to be using ADO for the Database connection, the % character as the Wildcard in the SQL will work in ORACLE as in Access.

AW
0
 

Author Comment

by:cookster26
ID: 10757085
I am using ODBC to connect to the databases.

It has been suggested by a colleague that I investigate fn concat for the concatenation. I have looked this up on the net and it seems like this might be the way to go for that problem.

Can anyone help with the alpha-range problem ???
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10758866
i'd suggest that you code it as a between should be much faster....

e.g.

A - Bl

surname between 'A' and 'BLZZZZZZZZZZZZZZZZZZZ'    (z's to the length of the column


0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Expert Comment

by:paul_dewit
ID: 10768329
I agree that coding it as a 'between' is faster,
but this might cause problems if your field is defined as varChar.
So the between solution is only a good idea if used with a Char field with fixed length.
0
 

Author Comment

by:cookster26
ID: 10772882
The problem with entering BLZZZZZZZZZ to length of string is the "alpha-split" range will be input in a text box by the user. I do not wish for them to have to type this sort of entry. What I am doing is taking the entry from the text box and doing a VB "split" on it and then the arrsplit(0)  is the character that all results have to be greater than and arrsplit(1) is the character that all resulats have to be less than.

I've managed to get to a machine with Oracle on and tested this yesterday using parameters A-BL. It returned all rows correctly EXCEPT the one row that had a surname beginning BL. (So instead of returning 35 rows it returned 34). For some reason doing A-BL was not inclusive.

I also tried between and this was the same...... how can I get it to be inclusive without the using having to type the ZZZZZ's ??
0
 

Author Comment

by:cookster26
ID: 10773553
Ok here is the solution:

strSQL = "Select {fn concat([surname field], {fn concat('  ', [initials field])})} as name, [servicenumber field] as serno " & _
                  " FROM [table name] " & _
                  " WHERE {fn substring([surname field],1," & _
Len(larrAlphaSplitVals(0)) & ")} >= '" & larrAlphaSplitVals(0) & "' " & _
                  " AND {fn substring([surname field],1," & _
Len(larrAlphaSplitVals(1)) & ")} <= '" & larrAlphaSplitVals(1) & "' " & _
                  " ORDER BY [surname field]"

this has been confirmed as working in access and oracle.

how can I get my points back ?? This was my first post on this forum and as no-one from here helped me, I don't think I should allocate any points.. .... is that how things work here ?
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 10987066
PAQed, with points refunded (100)

ee_ai_construct - (re-order part number #xm34)
Community Support Moderator
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question