Help in query with like - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help in query with like

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-07-01 : 06:44:32

I have a query where i were inputting a number and searching it in a fields last characters
Declare @input varchar(500)

--let InputVariable = '003'

SELECT * from REF_ADDRESS_LIST_FILE
WHERE REF_ADDRESS_LIST_FILE.RAL_DS_PHYSADD_ZIP4CODE
LIKE @InputVariable + '%'

it is giving correct result

Now I have to search it on multiple input fields

--let InputVariable = '350','350','351','352','354','355','356','357','358','359','360','361','362','363','364','365','366','367','368'

How can I run this query with wild search on the fileds last characters

SELECT * from REF_ADDRESS_LIST_FILE
WHERE REF_ADDRESS_LIST_FILE.RAL_DS_PHYSADD_ZIP4CODE
LIKE @InputVariable + '%'

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-07-01 : 06:53:51

I were trying
select * from REF_ADDRESS_LIST_FILE
WHERE substring(RAL_DS_PHYSADD_ZIP4CODE,7,3) in ('350','350','351','352','354','355','356','357','358','359','360','361','362','363','364','365','366','367','368')
but can't do it via parameter

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 06:57:14

SELECT * from REF_ADDRESS_LIST_FILE
WHERE ','+@InputVariable + ',' like '%,'+cast(REF_ADDRESS_LIST_FILE.RAL_DS_PHYSADD_ZIP4CODE as varchar(10))+',%'

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-07-01 : 07:01:12

Search for a split funtion...
And use the below query

[/code]

DECLARE @InputVariable AS VARCHAR(MAX)
SET @InputVariable = '350,350,351,352,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368'

SELECT * FROM REF_ADDRESS_LIST_FILE
CROSS APPLY ( SELECT Data FROM Split(@InputVariable, ',') ) a
WHERE REF_ADDRESS_LIST_FILE.RAL_DS_PHYSADD_ZIP4CODE LIKE a.Data + '%'

[/code]

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-07-01 : 07:14:51

thanks vaibhavktiwari83

Declare @InputVariable as varchar(8000)
set @InputVariable = '350,350,351,352,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368'

SELECT DISTINCT REF_ADDRESS_LIST_FILE.*,
LTrim(RTrim(REF_ADDRESS_LIST_FILE.RAL_DROPSHIP_DISCOUNTTYPE_CODE))
+ '--' + LTrim(RTrim(REF_ADDRESS_LIST_FILE.RAL_DROPSITE_NAME)) as EntryPointName
FROM REF_ADDRESS_LIST_FILE
WHERE ','+@InputVariable + ',' like '%,'+cast(REF_ADDRESS_LIST_FILE.RAL_DS_PHYSADD_ZIP4CODE as varchar(3))+',%'

Only problem i am having is that it is searching on first three character match while i have to search on last three characters

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 08:09:15

SELECT DISTINCT REF_ADDRESS_LIST_FILE.*,
LTrim(RTrim(REF_ADDRESS_LIST_FILE.RAL_DROPSHIP_DISCOUNTTYPE_CODE))
+ '--' + LTrim(RTrim(REF_ADDRESS_LIST_FILE.RAL_DROPSITE_NAME)) as EntryPointName
FROM REF_ADDRESS_LIST_FILE
WHERE ','+@InputVariable + ',' like '%,'+cast(right(REF_ADDRESS_LIST_FILE.RAL_DS_PHYSADD_ZIP4CODE,3) as varchar(3))+',%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources