I have one written one query as per below. Its not giving result when i did sub- string.

Declare @Result varchar(max)

set @Result=(select top 1 value from empkeyValue)

select  * 
from department d 
where (SUBSTRING('department4', 1, 11)+Convert(varchar(10), 4)) = @Result

Here when i wrote this condition (SUBSTRING('department4', 1, 11)+Convert(varchar(10), 4)) = @Result , there is no output. What is wrong in this query. please help me. SUBSTRING is having some issue. i m not able to find what is the problem here. after substring and concatenation with 4, it will give department4 which is actual column in table.

If i write like this as per below query, it will give result. But I do not want to hard code department4. It should be dynamic as per above query.

select  * from department d where department4= @Result

closed as off-topic by mustaccio, kevinsky, Max Vernon, dezso, Colin 't Hart Apr 17 at 19:16

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, kevinsky, dezso
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Sample data and expected results would help here... especially for values in the column of deptartment4, if that is an actual column. – scsimon Apr 17 at 14:09
  • 1
    Run select SUBSTRING('department4', 1, 11) and you'll see what's wrong. – mustaccio Apr 17 at 14:14
  • SUBSTRING('department4', 1, 11) equals LEFT('department4', 11) and gives an expected result 'department4'. What is wrong? – Denis Rubashkin Apr 17 at 14:25
  • So department4 is actually a column name? – mustaccio Apr 17 at 14:37
  • SUBSTRING('department4', 1, 11) or LEFT('department4', 11) is there in where condition. I tried with Left() . still not giving result. select * from department d where left('department4',11) = @Result. If i wrote select left('department4',11), this will give result. My problem is that in where condition its not working. – kamal Apr 17 at 14:41

Declare @Result varchar(max)

set @Result=(select top 1 value from empkeyValue)

select * from department d where (SUBSTRING('department4', 1, 11)+Convert(varchar(10), 4)) = @Result

First: Unquote 'department4', if it is a column name you must not quote it.

SUBSTRING(department4, 1, 11)

or as Denis Rubashkin has pointed out in comments

LEFT(department4, 11)

Second: Instead of SUBSTRING(department4, 1, 11) + CONVERT(VARCHAR(10), 4) you can use:

SUBSTRING(department4, 1, 11) + '4'


CONCAT(SUBSTRING(department4, 1, 11), '4')

Third: You can get the same result using a single command:

    department d
    CONCAT(LEFT(department4, 11), '4') = (SELECT TOP 1 value FROM empkeyValue);
  • SUBSTRING('department4', 1, 11) - i can not remove qoutes for department4, because i have department1,department2,department3,department4 column is present. i want to use dynamically department name as per my query. like for example department+'1'=department1 -- here department should take along with concat 1= then it should understand department1 as my actual column. – kamal Apr 17 at 15:53
  • Then you should rewrite your question just to let it clear – McNets Apr 17 at 16:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.