Wildcards in Replace Function

[travisbrown]

Can I use % in the second argument of a replace function? I want to replace everything right of ?sess_GUID=


SELECT *, REPLACE(REPLACE(log_referrer, '

http://edmssappp1/finance/fbt/',

''), '?sess_GUID=%', '') AS ref_page
FROM dbo.LOG_DATA
WHERE (log_GUID LIKE '%')
ORDER BY log_datetime

 

[SQLBill]

It won't work the way you want. Here's what the replace looks like

SELECT REPLACE(REPLACE(log_referrer, '

http://edmssappp1/finance/fbt/',

''), , '?sess_GUID=%', '')

REPLACE takes the second string ('?sess_GUID=%'), looks for it in the first string (REPLACE(log_referrer, '

http://edmssappp1/finance/fbt/',

'')) and then replaces it with the third string ('').

So what ends up happening is that you are replacing ?sess_GUID=%.

Can you give an example of what the original data looks like and what you want it to end up looking like?


-SQLBill

 

[tlbroadbent]

That is not possible. You should coonssider using the LEFT function. Would the followiong work for you?

SELECT
*, ref_page =
REPLACE(LEFT(log_referrer,
charindex('?sess_GUID=', log_referrer) - 1),
'

http://edmssappp1/finance/fbt/',

'')
FROM dbo.LOG_DATA
WHERE (log_GUID LIKE '%')
ORDER BY log_datetime Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLBill]

travisbrown,

what exactly are you trying to do? As I looked at Terry's solution for you and your original post, I realized that you appear to be removing the URL.

Remember, REPLACE looks for the second string within the first string and then replaces the second string with the third string. SO, you are effectively taking:

http://edmssappp1/finance/fbt/

and replacing it with nothing (in effect, deleting it).

You do the same thing with the ?sess_GUID= part. So, if you had an URL of:

http://edmssappp1/finance/fbt/?sess_GUID=12345

you would end up with:

12345

Is that what you are looking for?

-SQLBill

 

[travisbrown]

I have a column of URLS. I want to "trim" all urls with '

http://edmssappp1/finance/fbt/:

The results of the inner replace leaves

stats/session_details.asp?sess_GUID={EECF9EB4-ABB1-4250-A1B9-F53BEC3CE654}

or bins/content_page.asp?cid=1-23-345-34

I want to then cut everything to the left of stats/session_details.asp This is what I was trying to do with the outer replace. The GUID paramater is variable length, as is the CID.

 

[tlbroadbent]

Your statements seem to conflict. The first post says that you "replace everything right of ?sess_GUID=" but the latest statement says, "I want to cut everything to the left of stats/session_details.asp"

Please show an example of a string from the table and the expected result. I believe the query will be very simple ut need to know for sure before suggestion another alternative. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[travisbrown]

Sorry. Dyslexia for yet cure not found. I was thinking too hard.

Two Possible URLS

http://edmssappp1/finance/fbt/bins/content_page.asp?cid=1-45

http://edmssappp1/finance/fbt/stats...s_GUID={EECF9EB4-ABB1-4250-A1B9-F53BEC3CE654}

I want to cut

http://edmssappp1/finance/fbt/

and

?sess_GUID={EECF9EB4-ABB1-4250-A1B9-F53BEC3CE654}

 

[tlbroadbent]

The first answer I provided should give the result you describe for sess_guid... The following should work for both.

SELECT
*, ref_page =
REPLACE(LEFT(log_referrer,
charindex('?', log_referrer) - 1),
'

http://edmssappp1/finance/fbt/',

'')
FROM dbo.LOG_DATA
WHERE (log_GUID LIKE '%')
ORDER BY log_datetime
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[travisbrown]

Thanks Terry. I see what you are doing and it is exactly what i want. I get this message, though.

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

 

[tlbroadbent]

That will happen when charindex returns 0 because the string doesn't exist in the column. Try this...

SELECT
*, ref_page =
REPLACE(LEFT(log_referrer,
charindex('?', log_referrer) - 1),
'

http://edmssappp1/finance/fbt/',

'')
FROM dbo.LOG_DATA
WHERE (log_GUID LIKE '%')
AND charindex('?', log_referrer)>0
ORDER BY log_datetime
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[travisbrown]

Terry, this cuts the query string off both urls now though.

I need the QS on the content_page.asp?cid=1-45-... because there are other urls that end in cid=... (i.e. different hostname or directory)

Thanks for the help though. I think I can take it from here, based on the direction you've pointed me in.

 

[tlbroadbent]

Great. Good luck. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.