SSRS05 I trying to do a select from the first character after a space in a concatenate variable @name. You can see it has variable length and the space is either in position 2 or 3. @name = 1 companyaaaa 2 companycccc 12 companyxxxxxxxx 13 companysssssssssssssss select * from companies where companyname = substring(@name,charindex(' ',@name,1),LEN(@name)) this is not working, thanks.

Your formula seems ok except that you want to start the charindex at 0 instead of 1. What are the results that you get? do you get an error?Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

It's runs the drop down box has the 1 companyzzzz or 22 companycc in it but it isn't finding a match in the database so no rows are being returned?

anyone? (dataset country)select distinct country from country, @country = e.g. Hong Kong (dataset buyer) select convert(varchar(5),company_id)+ ' ' + companyname as name, companyname as buyer from companies where country = @country @name = e.g 22 hkcompanyname (dataset data) select * from companies where companyname = substring(@name,charindex(' ',@name,0),LEN(@name)) In the database the company id and company name are in different columns, so which is why there isn't a match. Hence me trying to use substring function to search for the name without the id. It runs but it doesn't find a match, so there must be something with the substring and charindex funcitons? Thanks

select * from companies where companyname = substring(@name,charindex(' ',@name,1)+1,LEN(@name)) select * from companies where companyname = Ltrim(substring(@name,charindex(' ',@name,1)+1,LEN(@name))) You haven't removed the space from the substring. And what is the data in the companies table? Regards,Eshwar. Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

Thanks Eswararao, thats the answer can you explain your code?

Trouble-shooting 101 (better than any direct answer you will get): instead of this select * from companies where companyname = substring(@name,charindex(' ',@name,1),LEN(@name)) go to an sql query window and run this select 'xxx'+substring([companyName],charindex(' ',[companyName],1),LEN(@name))+'xxx'from companies and you will be able to see what is going on. From there make adjustments. hope this helps, countryStyle

select * from companies where companyname = substring(@name,charindex(' ',@name,1)+1,LEN(@name)) charindex(' ',@name,1) will return 2 in case of '1 xxxxcompany' or 3 in case of '12 xxxcompany' but we should take from 3 or 4 so you have add 1 to the charindex(' ',@name,1)+1 Note:Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users. Regards,Eshwar.

adding 1 makes sense, what about the LTRIM?

It will trim empty spaces if there any in the string like '1 xxxcompany' will return ' xxxcompany' and if you ltrim it will remove empty space and will give 'xxxcompany' Regards,Eshwar.Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.