scalar function in select

Question

I'm trying to use a function to strip anything but numeric characters from a column in a select statement. I'm getting the error that the multi-part identifier could not be bound on the line calling the function.

Here is my function

 create function scrubNetWt (@input varchar(50)) returns varchar(50) AS Begin Return Left( SubString(@input, PatIndex('%[0-9.-]%', @input), 8000), PatIndex('%[^0-9.-]%', SubString(@input, PatIndex('%[0-9.-]%', @input), 8000) + 'X')-1) End

Here is my select statement where I'm trying to use the function

 USE [TFP] GO SELECT [pick_ticket_id] ,[pick_ticket_no] ,[proform_net_wt] ,[proform_cr_no] ,[TFP].[dbo].[scrubNetWt(proform_net_wt)] AS net_wt FROM [TFP].[dbo].[pick_ticket_ud]

Move the misplaced bracket: [TFP].[dbo].[scrubNetWt](proform_net_wt). — Dan Guzman
– Dan Guzman, Commented May 23, 2018 at 21:09
personally I'd get rid of all the square brackets there. they arent needed for any of your object or column names — Martin Smith
– Martin Smith, Commented May 23, 2018 at 21:12
Ha! thank you very much. yep. it works. would you believe I just wasted an hour on this. — ed2_d2
– ed2_d2, Commented May 23, 2018 at 21:13
is this your completed function? It doesnt do what you describe. it looks like it extracts a single section of digits. — Martin Smith
– Martin Smith, Commented May 23, 2018 at 21:22
it is working. I wanted it to extract a single section of digits because there are a few instances where users have entered values for this column that look like this "1.14 KG / 2.50" when all it really needs is 1.14. No idea where the 2.50 came from. I did just run it on my data and it is returning the value I need for the report — ed2_d2
– ed2_d2, Commented May 23, 2018 at 21:26

Juan Carlos Oropeza · Accepted Answer · 2018-05-23 21:09:29Z

1

Try

[TFP].[dbo].[scrubNetWt] (proform_net_wt)

answered May 23, 2018 at 21:09

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

scalar function in select

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related