removing everything after a . in a select

[Larshg]

Hi

I Have a table where I need to select only the text infront of . in a field.

ex
table1
hallo.nu
hey.nu
hay

I want the select to only return
table1
hallo
hey
hay

I tryede the locate and substr - but I can't quite get it to work.

/Larshg

 

[KenReay]

Hi

Your example and what you say you want do not match up

try

SELECT Left([YourField],IIF(Instr(1,".",[YourField])=0,Len([YourField]),Instr(1,".",[YourField]))) As A FROM tblYourTable;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lespaul]

what about something like:

SELECT iif(instr(fieldname, ".") > 0, substring(fieldname,instr(fieldname, "."), length(fieldname)), fieldname) FROM TableName

replace TableName and fieldname

Leslie

(ps - I may have the InStr backwards, it may be:

InStr(".", fieldname)

la)

 

[Larshg]

I can't quite get the result that I'm looking for.

The tabel that I have look something like this
hallo.nu
hey.nu
hay

I want the select to only return
hallo
hey
hay

That meens that if there is a "." then what even is after that should be removede.

KenRaey - I get exactely the same result using your select as I do wit select *

Lespaul - the substring dosen't work - the error is
Undefined funktion 'substring' in expression.

Hope someone can help.

Larshg

 

[KenReay]

hi
think substr() should be mid()

or you could use left() as I have

but you say that does not work?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Larshg]

Hi

The select I made from yours looks like this
SELECT Left([WM_MultiCenterIntegration.MC_Nodename],IIF(Instr(1,".",[WM_MultiCenterIntegration.MC_Nodename])=0,Len([WM_MultiCenterIntegration.MC_Nodename]),Instr(1,".",[WM_MultiCenterIntegration.MC_Nodename]))) As A FROM WM_MultiCenterIntegration;

the result is
NEWDELHI.DMT.DK
ODIN

And the result I'm looking for is
NEWDELHI
ODIN

If I replace substr with mid in Lespaul's select it complains about funktion 'length'

/Larshg

 

[KenReay]

Hi

Yes Length() should be Len()

I think LesPaul has given you XBase Synatx, I am a bit rsuty on it now but SubstR() and Length() I think are XBase language elements

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Larshg]

He He almost

now the sql ran thou, but I got this result


.DMT.DK
ODIN

It should have been
NEWDELHI
ODIN

/Lars

 

[KenReay]

So, let us see your SQL now

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Have you tried this ?
SELECT IIf(InStr(MC_Nodename,".")=0,MC_Nodename,Left(MC_Nodename,InStr(MC_Nodename,".")-1)) As Nodename
FROM WM_MultiCenterIntegration;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Larshg]

Here it is.

SELECT iif(instr(FIELD, ".") > 0, mid(FIELD,instr(FIELD, "."), len(FIELD)), FIELD) FROM TABEL;

/Larshg

 

[Larshg]

Thank you it works..

/Lars

 

[KenReay]

Hi

SELECT iif(instr(FIELD, ".") > 0, Left(FIELD,instr(FIELD, ".")-1), FIELD) FROM TABEL;

If you look up Mid() in help you will see why you were getting the .DK..etc


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK