sql string function question

In sql server I'm trying to assign the variable @lv_timss_print_prodid the value minus the '-PDF'

i.e. if the value is 83792-PDF, it becomes 83792

I am getting an incorrect syntax error

		IF @lv_prefix = 'MAN'
			BEGIN
				SET @lv_product = @lv_timss_prodid
				IF RIGHT(@lv_product, 4) != '-PDF'
					BEGIN
						RETURN
					END
				ELSE
					BEGIN
						-- @lv_timss_print_prodid = LEFT(@lv_product, LEN(@lv_product)-4);
						@lv_timss_print_prodid = SUBSTRING(@lv_product,0,LEN(@lv_product)-4);
					END
			END

Open in new window

LVL 2
PurpleSladeAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You are missing the "SET" statement, on this line:
                                    @lv_timss_print_prodid = SUBSTRING(@lv_product,0,LEN(@lv_product)-4);


In T-SQL, it should be:

                                    SET @lv_timss_print_prodid = SUBSTRING(@lv_product,0,LEN(@lv_product)-4);


[T-SQL does not allow the SET to be left off a variable assignment, it must be explicitly stated.]
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
LEFT works...

@lv_timss_print_prodid = LEFT(@lv_product, LEN(@lv_product)-4)
0
 
PurpleSladeAuthor Commented:
It looks like i have something else wrong - commenting out the else section allows it to run fine - can you see any other syntax error?
		IF @lv_prefix = 'MAN'
			BEGIN
				SET @lv_product = @lv_timss_prodid
				IF RIGHT(@lv_product, 4) != '-PDF'
					BEGIN
						RETURN
					END
				--ELSE
				--	BEGIN
				----		-- @lv_timss_print_prodid = LEFT(@lv_product, LEN(@lv_product)-4);
				----		-- @lv_timss_print_prodid = SUBSTRING(@lv_product,0,LEN(@lv_product)-4);
				--	END
			END

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copy-paste any error that is being returned into this question.
Also explain using RETURN where you have it.

Mind readers we ain't.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You just need to use a replace on your Else part

 
 return ( replace ( (@lv_product,'-PDF', '' ) )
0
 
ZberteocCommented:
As per aneeshattingal you don't need to build a function just for that, the right and the easiest way is to use the built in SQL function for string processing REPLACE:

SELECT REPLACE(col_name, '-PDF','') as col_name FROM your_table

-- or

SELECT * FROM your_table WHERE REPLACE(col_name, '-PDF','')=83792

Open in new window

As a note for the second query is that if there is an index on the col_name column it won't be used because the column's name is wrapped in the REPLACE function. But this is how it would behave with any function, built in or user defined.
0
 
PurpleSladeAuthor Commented:
That was the issue, thanks.
0
 
ZberteocCommented:
No offense but all that construct with the IF is not necessary if you would use REPLACE. Why all that complicated code to check if there is -PDF in the string and on top of that SUBSTRING? The idea behind EE is also to learn something to be done the right way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.