Solved

sql string function question

Posted on 2013-05-13
8
283 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 65

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 65

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 26

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 500 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 26

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now