Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql string function question

Posted on 2013-05-13
8
Medium Priority
?
289 Views
Last Modified: 2013-05-14
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
Comment
Question by:PurpleSlade
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39162842
LEFT works...

@lv_timss_print_prodid = LEFT(@lv_product, LEN(@lv_product)-4)
0
 
LVL 2

Author Comment

by:PurpleSlade
ID: 39162857
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39162891
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39162910
You just need to use a replace on your Else part

 
 return ( replace ( (@lv_product,'-PDF', '' ) )
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39162964
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39163208
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
 
LVL 2

Author Closing Comment

by:PurpleSlade
ID: 39163585
That was the issue, thanks.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39164175
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question