InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 12:53:47 GMT20http://www.sqlservercentral.com/Forums/Topic1077681-392-1.aspxThanks Sean! I was able to use datalength and it works successfully now. Thanks for everyone's help!Mon, 14 Mar 2011 07:49:57 GMTdustin.walkerhttp://www.sqlservercentral.com/Forums/Topic1077681-392-1.aspxAlso check that mdmpa.message_text does not have non space chars following "ALL MET ITEMS FOR VIN PROCESSED SUCCESSFULLY"Mon, 14 Mar 2011 07:41:51 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic1077681-392-1.aspx[quote]I think the issue is on "SUBSTRING(bd.buft_string_1, 35, 70) = mdmpa.message_text"[/quote]Did you prove this by removing the join and getting rows returned?If it is the substring then the most likely cause is either bd.buft_string_1 contains non space chars (hidden or otherwise) after "ALL MET ITEMS FOR VIN PROCESSED SUCCESSFULLY" or the text does not start at column 35.What is the size of bd.buft_string_1?Mon, 14 Mar 2011 07:39:40 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic1077681-392-1.aspxTry using datalength instead of a hardcoded length. Think that should get you the desired result.Mon, 14 Mar 2011 07:38:31 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1077681-392-1.aspxCan someone please help! I'm having an issue with a query. It's using a SUBSTRING but I think there's a problem with it. It returns 0 rows when it should return 1. Here is the query in question. SELECT v.veh_id, vi.inventory_id, SUBSTRING(bd.buft_string_1, 35, 70), bd.* FROM buft_chrysler_data AS bd INNER JOIN vehicle AS v WITH (NOLOCK) ON SUBSTRING(bd.buft_string_1, 14, 17) = v.vin INNER JOIN vehicle_inventory AS vi WITH (NOLOCK) ON v.veh_id = vi.veh_id AND vi.own_dtm = (SELECT MAX(o.own_dtm) FROM ownership AS o WITH (NOLOCK) WHERE o.veh_id = v.veh_id AND o.tran_account_id = 1) -- Chrysler INNER JOIN message_dcx_met_part_audit AS mdmpa WITH (NOLOCK) --ON LTRIM(RTRIM(SUBSTRING(bd.buft_string_1, 35, 70))) = LTRIM(RTRIM(mdmpa.message_text)) ON SUBSTRING(bd.buft_string_1, 35, 70) = mdmpa.message_text WHERE bd.tran_event_id = 62139I think the issue is on "SUBSTRING(bd.buft_string_1, 35, 70) = mdmpa.message_text". The mdmpa.message_text field is a varchar(70), this is why we're using 70 as our Substring length. However, it doesn't seem to like this. It should match where message_text is equal to "ALL MET ITEMS FOR VIN PROCESSED SUCCESSFULLY". If I simply use "SUBSTRING(bd.buft_string_1, 35, 44) instead where 44 is the exact length of the string it works just fine. How do I account for a varchar field though?Any help would be greatly appreciated!Mon, 14 Mar 2011 07:13:28 GMTdustin.walker