Extract name from String? 1

[NavMen]

Hi,

I'm trying to extract from the following string only the name: Irma Schmidt

String from the DB:

EX:"Irma Schmidt"</o=myexchange/ou=first administrative group/cn=recipients/cn=irma>

And when I'running the query I got 1000 mailaddresses from the DB and I like only the first 10.

Thanks,

naVmen

 

[bborissov]

declare @Test varchar(200)
SET @Test = '"Irma Schmidt"</o=myexchange/ou=first administrative group/cn=recipients/cn=irma>'
SELECT REPLACE(LEFT(@Test, CHARINDEX('<',@Test)-1),'"','')

If you want ony 10 results then use TOP 10 with ORDER BY clause.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[NavMen]

Thnx Borisiav,

But the string start with EX:"name"< > or SMTP:"name"< > etc

I got now EX:Irma Schmidt . How can I remove the words before the first "

Thanks

NavMen

 

[bborissov]

O!
I thought that EX: is a part of "Example"

declare @Test varchar(200)
SET @Test = 'EX:"Irma Schmidt"</o=myexchange/ou=first administrative group/cn=recipients/cn=irma>'
SELECT SUBSTRING(@Test, 5, CHARINDEX('<',@Test)-6)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[NavMen]

last question

we have also this string:

SMTP:"John McAen"<John_McAen@mymail.local>

or

NOTES:"Es ter Horst"<notes/IBM/Es >

with this current query I got :"John McAen or S:Es ter Horst

Can we remove text before the first " (and also the ")

Thnx,

NavMan

 

[bborissov]

NavMen,
I can't imagine what you CAN have in that field.
You could have anything in it.
The only thing that I think is sure that you have [<] AFTER that name. So, How can I give you suggestion that SHOULD work when I don't know ALL possible combinations.
WHAT distinct the name from other part of the string?
Is this ["] - double quotes?
Is this [:]?
What is it?
How do you know what is the name?

If I give you example how to extract the name from the first two ["] are you sure that this would be right?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[NavMen]

Yep, you are right.

This is the list we got from the DB


EX:"exMgr"</o=myexchange/ou=first administrative group/cn=recipients/cn=exmgr>
SMTP:"'John@hotmail.com'"<John@hotmail.com>
EX:"Jac Schmidt"</o=myexchange/ou=first administrative group/cn=recipients/cn=jac>
EX:"exMgr"</o=myexchange/ou=first administrative group/cn=recipients/cn=exmgr>
EX:"Irma Schmidt"</o=myexchange/ou=first administrative group/cn=recipients/cn=irma>
EX:"Ken Pol"</o=myexchange/ou=first administrative group/cn=recipients/cn=ken>
EX:"Jane McCean"</o=myexchange/ou=first administrative group/cn=recipients/cn=jane>
EX:"exMgr"</o=myexchange/ou=first administrative group/cn=recipients/cn=exmgr>
EX:"John van Spelt"</o=myexchange/ou=first administrative group/cn=recipients/cn=john>
EX:"exMgr"</o=myexchange/ou=first administrative group/cn=recipients/cn=exmgr>
NOTES:"Esther Wijk"</o=notes/ou=ibm/cn=users/cn=esther>

Between ["]firstname lastname["] is what I want.

Thanks!!

Navmen

 

[bborissov]

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] ([COLOR=blue]Names[/color] [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"exMgr"</o=myexchange/ou=first administrative group/cn=recipients/cn=exmgr>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'SMTP:"''John@hotmail.com''"<John@hotmail.com>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"Jac Schmidt"</o=myexchange/ou=first administrative group/cn=recipients/cn=jac>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"exMgr"</o=myexchange/ou=first administrative group/cn=recipients/cn=exmgr>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"Irma Schmidt"</o=myexchange/ou=first administrative group/cn=recipients/cn=irma>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"Ken Pol"</o=myexchange/ou=first administrative group/cn=recipients/cn=ken>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"Jane McCean"</o=myexchange/ou=first administrative group/cn=recipients/cn=jane>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"exMgr"</o=myexchange/ou=first administrative group/cn=recipients/cn=exmgr>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"John van Spelt"</o=myexchange/ou=first administrative group/cn=recipients/cn=john>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'EX:"exMgr"</o=myexchange/ou=first administrative group/cn=recipients/cn=exmgr>'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color] ([COLOR=red]'NOTES:"Esther Wijk"</o=notes/ou=ibm/cn=users/cn=esther>'[/color])
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]LEFT[/color]([COLOR=#FF00FF]SUBSTRING[/color]([COLOR=blue]Names[/color], [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'"'[/color],[COLOR=blue]Names[/color])+1,8000),[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'"'[/color],[COLOR=#FF00FF]SUBSTRING[/color]([COLOR=blue]Names[/color], [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'"'[/color],[COLOR=blue]Names[/color])+1,8000))-1)
[COLOR=blue]FROM[/color] @Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jbenson001]

Using Borislav's data, I came up with this:

Select SUBSTRING( Names, CHARINDEX ( '"', Names ) + 1, CHARINDEX ( '"', Names, CHARINDEX ( '"', Names ) + 1 ) - CHARINDEX ( '"', Names ) - 1)
From @Test

 

[NavMen]

Borislav, Thanks for the support it works!!

Cheers,

Navman