March 19, 2009 at 6:23 am
I've got a sql query, which gives a result like "01-09-2008 09:07 MYNAME: This is the result text...."
What I like to got is the same result, without the "01-09-2008 09:07 MYNAME: " so the result would be "This is the result text...."
I've been looking around for a solution (ltrim, rtrim, stuff) but I can't get it working.
Can someone help?
Query:
select
incident.naam as Incident_Nr,
incident.datumaangemeld as Datum_Aanmelden,
incident.verzoek as Omschrijving
from
incident
INNER JOIN classificatie on incident.incident_domeinid=classificatie.unid
INNER JOIN soortbinnenkomst on incident.soortbinnenkomstid=soortbinnenkomst.unid
INNER JOIN vestiging on incident.aanmeldervestigingid = vestiging.unid
where
soortbinnenkomst.naam='waarde1'
and classificatie.naam='waarde2'
and vestiging.debiteurennummer = @P_Relatie
order by
Incident_Nr
March 19, 2009 at 6:32 am
are always going to remove everything before and including "..MYNAME:" ?
Is so then here is solution
DECLARE @string VARCHAR(MAX)
SET @string = '01-09-2008 09:07 MYNAME: This is the result text....'
SELECT SUBSTRING(@string,PATINDEX('%MYNAME: %',@string)+8,LEN(@string))
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 6:38 am
not sure which is faster but here is another:
SELECT RIGHT(@String,PATINDEX('% :EMANYM%',REVERSE(@string))-1)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 6:59 am
First of all, thanks for the quick reply.
But....
The results is a list of results from the table "incident.verzoek as Omschrijving"
So I've got multiple results I like to alter.
Incident_NrDatum_Aanmelden Omschrijving
I010101 0011-9-2008 "01-09-2008 09:07 MYNAME: This is the result text...."
I010101 00217-3-2009 "17-03-2009 11:17 MYNAME: This is the result text...."
I010101 00313-2-2009 "13-02-2009 09:22 MYNAME: This is the result text...."
March 19, 2009 at 7:01 am
just replace my variable with your column name in your code and your done...
for example:
select
incident.naam as Incident_Nr,
incident.datumaangemeld as Datum_Aanmelden,
incident.verzoek as Omschrijving,
RIGHT(incident.verzoek,PATINDEX('% :EMANYM%',REVERSE(incident.verzoek))-1)
from
incident
INNER JOIN classificatie on incident.incident_domeinid=classificatie.unid
INNER JOIN soortbinnenkomst on incident.soortbinnenkomstid=soortbinnenkomst.unid
INNER JOIN vestiging on incident.aanmeldervestigingid = vestiging.unid
where
soortbinnenkomst.naam='waarde1'
and classificatie.naam='waarde2'
and vestiging.debiteurennummer = @P_Relatie
order by
Incident_Nr
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 7:13 am
I got the next errormessage:
Argument data type ntext is invalid for argument 1 of reverse function
March 19, 2009 at 7:15 am
ah you using a text field, that changes things
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 7:20 am
try this one.
SUBSTRING([YOURCOLUMN],PATINDEX('%MYNAME: %',[YOURCOLUMN])+8,DATALENGTH([YOURCOLUMN]))
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 7:36 am
COOL 😀
It works! Many thanks and another sql thing learned!
March 19, 2009 at 8:22 am
Related question:
What if I like to do the same, but than on the right site?
Query result (table)
DC01_CUSTOMER
CS01_CUSTOMER
FileServerName_CUSTOMER
lose the _CUSTOMER, so the result is:
DC01
CS01
FileServerName
March 19, 2009 at 9:02 am
if it's always going top be 4 charaters then just use SUBSTRING with a lenght of 4.
Look at BOL for how to use substring
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 9:12 am
In this case it's not always 4....
The structure is always the same: aaaaaa_BBBBBBB
But aaaaa can be a different length and BBBBBB also.
I'dd like to get rid of the "_BBBBBBBB" part.
March 19, 2009 at 9:26 am
I'm gonna try and teach you to work this one out, so you can understand how some of the SQL functions work.
What you need to do is this. (use BOL for the correct syntax)
Use this PATINDEX for finding where the "_" is in the string
The Use SUBSTRING to extract all the letters you want , starting from the beginning of the string and ending at the postion you found the "_"?
If you column was a VARCHAR and not a TEXT then you could use LEFT instead of SUBSTRING
Does this make sense?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 19, 2009 at 9:54 am
Thanks for the help. I'm going to try make it work!
One difference, I've to delete the characters AFTER the _
I hope this works the same way?
March 19, 2009 at 9:56 am
cool and if you find the solution could you post it here for others who might be trying the same thing.
Or if you have questions post what you have so far and I can try and help you further
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply