• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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

0
PurpleSlade
Asked:
PurpleSlade
  • 2
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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
 
Scott PletcherSenior 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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now