SQL Server - using # in a string during a search

Question

Hi

In our datatable, we have a ton of values that contain # because typically in construction, fasteners are graded with a # like this

'#8 x 2-1/2in

When i try to search for this in a Stored Procedure to filter the fastener size down, it fails because of the hash. I know its because the # has a meaning in a SQL statement so how would i get around it and find the rows using the hash in the search criteria?

Thanks

Nacho

Answer 1

FYI - you should use table aliases and reference the alias...for example:

SELECT mt.part_id, mt.part_type_name, mt.KeySearch1, mt.Description
 FROM    dbo.MyTable As mt
 WHERE (mt.KeySearch1 = @KeySearch1 or @KeySearch1 = NULL)

You also do not need (or want) an explicit transaction for a select statement - you only need that when you are inserting/updating/deleting data across multiple tables and they all need to either commit as one transaction or roll back as one transaction.

Answer 2

Hi,

Try :

    USE [dbtest]
    GO
    /****** Object:  StoredProcedure [dbo].[usp1]    Script Date: 12/15/2020 9:50:30 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[usp1] 
     -- Add the parameters for the stored procedure here

       @KeySearch1 varchar(255) = NULL,
  @part_id int = 0,
 @part_type_name varchar(255) = 0
     AS 
     SET NOCOUNT ON 
     SET XACT_ABORT ON  

     BEGIN TRAN

     SELECT  dbo.MyTable.part_id, dbo.MyTable.part_type_name, dbo.MyTable.KeySearch1, dbo.MyTable.Description
     FROM    dbo.MyTable
     WHERE (dbo.MyTable.KeySearch1 = @KeySearch1 or @KeySearch1 IS NULL)

     COMMIT

Answer 3

Managed to figure it out, i think it was a series of typos on my behalf....

In the stored procedure, the parameters should have been

 @part_id int = 0,
 @part_type_name varchar(255) = NULL,
 @KeySearch1 varchar(255) = NULL

in the WHERE clause, it should have been like this

WHERE (dbo.MyTable.KeySearch1 = @KeySearch1 or @KeySearch1 IS NULL)

Answer 4

Hi

Thanks for the responses. I have a search field in my table that can take any value used to filter a table. The same table holds all types (hardware, paint, etc) in the same structure

Create the table

CREATE TABLE MyDB.MyTable (
    part_id INT PRIMARY KEY IDENTITY (1, 1),
    part_type_name VARCHAR (255) NULL,
    KeySearch1 VARCHAR (255) NULL,
    Description VARCHAR(255) NULL
);

Insert data

INSERT INTO MyDB.MyTable (part_type_name, KeySearch1, Description)
VALUES 
('SCREWS', '#6', '#6 x 2-1/2in MACHINE SCREW'),
('SCREWS', '#6', '#6 x 2-1/2in MACHINE SCREW'),
('SCREWS', '#8', '#8 x 2-1/2in MACHINE SCREW'),
('SCREWS', '#8', '#8 x 2-1/2in MACHINE SCREW'),
('SCREWS', '#10', '#10 x 2-1/2in MACHINE SCREW');

Stored Proc

USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_MyTableSelect] 

@part_id int = 0,
@part_type_name varchar(255) = 0,
@KeySearch1 varchar(255) = NULL
AS 
SET NOCOUNT ON 
SET XACT_ABORT ON  

BEGIN TRAN

SELECT dbo.MyTable.part_id, dbo.MyTable.part_type_name, dbo.MyTable.KeySearch1, dbo.MyTable.Description,
FROM    dbo.MyTable
WHERE (dbo.MyTable.KeySearch1 = @KeySearch1 or @KeySearch1 = NULL)

COMMIT

-- [dbo].[usp_MyTableSelect] #6

Error
Msg 245, Level 16, State 1, Procedure usp_MyTableSelect, Line 10 [Batch Start Line 19]
Conversion failed when converting the varchar value '#6' to data type int.

Answer 5

How is it failing - what error are you getting and what does your code look like?

Declare @myString varchar(20) = '#8 x 2-1/2in';
  Print iif(@myString Like '#%', 1, 0)
  Print iif(substring(@myString, 1, 1) = '#', 1, 0)