CASE in TSQL 3

[Jesus4u]

Before I update I want to test whether certain conditions are true but I get the following error.
Why?
Thanks

[error]
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '>'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'as'.
[/error]

UPDATE W 
	SET Referrer=

		CASE Referrer 
			WHEN CHARINDEX('[URL unfurl="true"]http://',[/URL] Y.PR) > 0 THEN REPLACE(Y.PR, '[URL unfurl="true"]http://',[/URL] '')
			WHEN CHARINDEX('[URL unfurl="true"]http://www.',[/URL] Y.PR) > 0 THEN REPLACE(Y.PR, '[URL unfurl="true"]http://www.',[/URL] '')
			ELSE Y.PR
		END


	FROM WebLog W 
	INNER JOIN 
	 (SELECT
	   IP,
	  (Referrer) as PR 
	  FROM WebLog l
	  WHERE [Date] =
	     (SELECT Max([Date])
	      FROM WebLog
	      WHERE IP=l.IP)) as Y 
	  ON W.IP=Y.IP

Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[cheyney]

take out the 'Referrer' after the CASE keyword

like this

SET Referrer=

CASE
WHEN CHARINDEX('

http://',

Y.PR) > 0 THEN REPLACE(Y.PR, '

http://',

'')
WHEN CHARINDEX('

http://www.',

Y.PR) > 0 THEN REPLACE(Y.PR, '

http://www.',

'')
ELSE Y.PR
END

Cheyney

 

[sunila7]

Hi,

Try this query..... hope it helps

UPDATE W
SET Referrer=

CASE
WHEN CHARINDEX('

http://',

Y.PR) > 0 THEN REPLACE(Y.PR, '

http://',

'')
WHEN CHARINDEX('

http://www.',

Y.PR) > 0 THEN REPLACE(Y.PR, '

http://www.',

'')
ELSE Y.PR
END


FROM WebLog W
INNER JOIN
(SELECT
IP,
(Referrer) as PR
FROM WebLog l
WHERE [Date] =
(SELECT Max([Date])
FROM WebLog
WHERE IP=l.IP)) as Y
ON W.IP=Y.IP

Sunil

 

[Jesus4u]

Both of your suggestions worked! BUT what I didn't realize is that BOTH WHENS fire off for each record. How can I just execute ONE condition if it is true?

Like this?

UPDATE W 
	SET Referrer=

		--CASE  
			IF (CHARINDEX('[URL unfurl="true"]http://',[/URL] Y.PR)) > 0 
				
				REPLACE(Y.PR, '[URL unfurl="true"]http://',[/URL] '')
				
			ELSE IF (CHARINDEX('[URL unfurl="true"]http://www.',[/URL] Y.PR)) > 0  
				REPLACE(Y.PR, '[URL unfurl="true"]http://www.',[/URL] '')
			--ELSE Y.PR
		--END


	FROM WebLog W 
	INNER JOIN 
	 (SELECT
	   IP,
	  Referrer as PR 
	  FROM WebLog l
	  WHERE [Date] =
	     (SELECT Max([Date])
	      FROM WebLog
	      WHERE IP=l.IP)) as Y 
	  ON W.IP=Y.IP

Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[cheyney]

how about just

UPDATE W
SET Referrer= replace(replace(Y.PR, '

http://www.',

''), '

http://',

'')


cheyney

 

[tlbroadbent]

CASE will only execute one statement. You must reverse the order of the WHEN clauses because the first WHEN will be true if the 2nd condition is true.

SET Referrer=
CASE
WHEN CHARINDEX('

http://www.',

Y.PR) > 0
THEN REPLACE(Y.PR, '

http://www.',

'')
WHEN CHARINDEX('

http://',

Y.PR) > 0
THEN REPLACE(Y.PR, '

http://',

'')
ELSE Y.PR
END

You don't even need the CASE function to perfrom your query.REPLACE will replace the substring if it exists otherwise it return the value of Y.PR with no change. You can code the statement thus.

SET Referrer = REPLACE(Y.PR, '

http://www.',

'') If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Jesus4u]

HA Thanks that was simple! Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[Jesus4u]

thanks guys Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[Jesus4u]

another thing please?

The LAST thing I need to do is remove everything to the right of the first occurence of a "/".
From:- SomeDomain.com/page.asp
To :- SomeDomain.com

But the code doesn't parse correctly.

UPDATE W 
	SET Referrer = 
		LEFT(replace(replace(Y.PR, '[URL unfurl="true"]http://www.',[/URL] ''), '[URL unfurl="true"]http://',[/URL] ''), CHARINDEX('/', Y.PR) )

	FROM WebLog W 
	INNER JOIN 
	 (SELECT
	   ID,
	  Referrer as PR 
	  FROM WebLog l
	  WHERE [Date] =
	     (SELECT Max([Date])
	      FROM WebLog
	      WHERE ID=l.ID)) as Y 
	  ON W.ID=Y.ID

Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[cheyney]

SET Referrer =
LEFT(replace(replace(Y.PR, '

http://www.',

''), '

http://',

''), CHARINDEX('/', replace(replace(Y.PR, '

http://www.',

''), '

http://',

'')) )

cheyney

 

[Jesus4u]

thank you! Exams Passed: 70-152, 70-175, 70-176, 70-100