Please help convert query from Excel to SQL...

queries

Hi,

First time poster to boards…

I know it’s a long shot but could anyone please convert this query into SQL to produce the result below?

The following data is in each field of a description column in SQL:

Test Data Prior to field Twenty ?20 Datafield-Twenty ?21 Datafield-Twentyone ?22 Datafield-Twentytwo ?30 Datafield-Thirty ?31 Datafield-Thirtyone ?32 Datafield-Thirtytwo ?33 Datafield-Thirtythree ?34, Datafield-Thirtyfour ?35 Datafield-Thirtyfive ?36 Datafield-Thirtysix ?60 Datafield-Sixty ?61 Datafield-Sixtyone ?62 Datafield-Sixtytwo ?63 Datafield-Sixtythree

Result Required:

Test Data Prior to field Twenty Datafield-Twenty Datafield-Sixty Datafield-Sixtyone Datafield-Sixtytwo Datafield-Sixtythree

I can produce the formulae in Excel but haven't a clue how to code it up in SQL:

'=LEFT(A2,(FIND("?20",A2,1)-1))&MID(A2,4+FIND("?20",A2,1),(FIND("?21",A2,1)-FIND("?20",A2,1)-4))&MID(A2,4+FIND("?60",A2,1),(FIND("?61",A2,1)-FIND("?60",A2,1)-4))&MID(A2,4+FIND("?61",A2,1),(FIND("?62",A2,1)-FIND("?61",A2,1)-4))&MID(A2,4+FIND("?62",A2,1),(FIND("?63",A2,1)-FIND("?62",A2,1)-4))&MID(A2,4+FIND("?63",A2,1),30)

Can anyone help please?

Thanks,

E.

ecaf

I'd give it a shot for you, but I don't understand your question at all. The ?'s is a bit off putting. All the test data, Datafield Twenty... hard to understand.

Result Required:

Test Data Prior to field Twenty Datafield-Twenty Datafield-Sixty Datafield-Sixtyone Datafield-Sixtytwo Datafield-Sixtythree

Can you just give the field names and what you want out of it? (maybe some sample values in the field and sample result values.

forbairt

as ecaf said ...

could you post up the excel sheet with an example set ?

queries

Looking for a good example at the moment...

queries

Here is the best example I can find

ecaf

Sorry have to head off but just a quick bit of help that might get you started.

I assumed that ?20, ?21, etc were seperate columns, if not you will need to modify this somewhat (sorry I just didn't have time to get any more complex than this). Also SQL doesn't have a mid function, so you have to use left() and right() together.

SELECT RIGHT(Column1,(LEN(Column1) - (CHARINDEX('?20', Column1)-1))) as Value1

Explanation:
Column1 = :86:005?00DIRECT DEBIT?109700/322?2012345678 ZBNR 2805130176

CHARINDEX('?20', Column1) finds where in the column ?20 started, i.e. at character 34

You already seem to have a knowledge of Left & Mid, so I'm assuming you understand my Right statement.... if not I get the total length of the column and subtract the starting point (34),
I was actually testing this out on a firstname / surname column and looking for part of a name in it, so looking back I think the "Len - (Charindex-1)" might actually be wrong, I think you might want something like Len - (Charindex...+3) - sorry just don't have time to work it out, but hopefully its a start.

MOH

ecaf wrote: »

Also SQL doesn't have a mid function, so you have to use left() and right() together.

Most implementations of SQL have an equivalent mid function of some sort - might not be called mid(), try substr() or substring().

Queries, what kind of SQL are you talking about?

queries

We are using Oracle 11.5.9 and the DBA is unable to code it correctly excluding/including all the relevant ? sections so I thought I'd see if anyone else on here would be able...

I don't have access to the code the DBA is using...

ecaf

I think it might be a lot simpler with Oracle because it has Instr() function, I only MS SQL so I'm not that familiar with Oracle.

Example of this is:
INSTR('abcabcabcdef','de') = 7 or in your case for ?20
INSTR('86:005?00DIRECT DEBIT?109700/322?2012345678 ZBNR 2805130176', '?20') = 33

So if you know ?20 starts at position 33 and runs for 3 characters, then you want the text right of this.

To get the text after the ?20 this is what you could do (maybe better ways of doing it, but here's my offering)

SELECT SUBSTR(<value>, <starting_position>, <number_of_characters>) FROM Table1.

INSTR (string , substring [, position [, occurrence]])

(NB String1 = '86:005?00DIRECT DEBIT?109700/322?2012345678 ZBNR 2805130176' )

SELECT SUBSTR('String1', INSTR('String1', '?20', 1, 1) + 3, LEN('String1') - (INSTR('String1', '?20', 1, 1)+3)) FROM Table1

This code should return '12345678 ZBNR 2805130176' because you are returning a substring of String1 starting at position 36 (given by INSTR + 3 function), and returning all the values for X number of characters.
Where X = Len(String1) - 36

I hope I haven't confused you more, can you access the table that the DBA has access to, so you can test this. Or maybe set up your own table and input the values into a field then try the select statement.

The rest of the code should be similar for ?21, ?22 etc.

queries

Thanks ecaf for your coding, we'll try it out.

I really need to learn SQL...

MOH

The excel function you gave doesn't seem to match the example - your example has no ?62 or ?63. And also, your function is going to include everything up to ?20.

Anyway, take your excel function, paste it into Word. Then:
- Replace all occurrences of MID with SUBSTR
- Replace all occurrences of A2 with your database field name, e.g. INPUT
- Replace all occurrences of FIND with INSTR
- Swap the first two parameters for each INSTR - so INSTR(INPUT,‘?20’, 1) instead of INSTR(‘?20’, INPUT, 1). Also, there's a 4th parameter on Oracle INSTR specifying which occurrence you want to find. Not sure if it's optional, if not you'll need an extra ,1 in each INSTR.
- Replace the & with the Oracle concatenation operator.
- Done.


So you should end up with something like:

LEFT(INPUT,(INSTR(INPUT,‘?20’,1)-1))
||SUBSTR(INPUT,4+INSTR(INPUT,‘?20’,1),(INSTR(INPUT,‘?21’,1)-INSTR(INPUT,‘?20’,1)-4))
||SUBSTR(INPUT,4+INSTR(INPUT,‘?60’,1),(INSTR(INPUT,‘?61’,1)-INSTR(INPUT,‘?60’,1)-4))
||SUBSTR(INPUT,4+INSTR(INPUT,‘?61’,1),(INSTR(INPUT,‘?62’,1)-INSTR(INPUT,‘?61’,1)-4))
||SUBSTR(INPUT,4+INSTR(INPUT,‘?62’,1),(INSTR(INPUT,‘?63’,1)-INSTR(INPUT,‘?62’,1)-4))
||SUBSTR(INPUT,4+INSTR(INPUT,‘?63’,1),30)

Although you may need to change the || if concatenation is different in Oracle.

queries

Thanks MOH,

On my excel example, I didn't mean to include the data before field ?20, my error, sorry!

This is what we are looking for:

Exclude all details before ?20
- ?20 always appears in the field

Include Details from ?20, ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29
- although ?20 will alway appear, ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29 may be available in some fields and not other fields in the same column

Exclude Details from ?30, ?31, ?32, ?33, ?34, ?35, ?36
- All these ?30, ?31, ?32, ?33, ?34, ?35, ?36 may be available in some fields and not other fields in the same column

Include Details from ?60, ?61, ?62, ?63
- All these ?60, ?61, ?62, ?63 may be available in some fields and not other fields in the same column

I'll try to update the code...

MOH

Once you get the Excel formula, the steps above should get you most of the way to the Oracle SQL.

But I think you're going to have problems if you're looking for e.g. ?21 and it's not there - this:

SUBSTR(INPUT,4+INSTR(INPUT,‘?20’,1),(INSTR(INPUT,‘?21’,1)-INSTR(INPUT,‘?20’,1)-4))

is going to give 0 or maybe -1 for INSTR(INPUT,‘?21’,1) if ?21 isn't in the string, so then you're subtracting something from that, and passing a negative value into SUBSTR, which isn't going to end well.

Don't even think that will work in Excel?

queries

Its getting pretty complicated alright, I'll have to leave looking at it. DBA trying to resolve it. Thanks for all your help.