Solved

sql string function question

Posted on 2013-05-13
8
284 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
push and Pull replication 31 46
Many to one in one row 2 35
Upgrading to SQL Server 2015 Express 2 28
SQL works but want to get the XML node data separately 11 25
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 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