InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 09:17:02 GMT20http://www.sqlservercentral.com/Forums/Topic548922-8-1.aspxThanks, KenSomeone else suggests a much simpler way:change [code]case when charindex('(', asset.asset_name) > 0 then left(asset.asset_name, charindex('(', asset.asset_name) - 1) else asset.asset_nameend asset_name, [/code]to[code] CAST(CASE WHEN charindex('(', asset.asset_name) > 0 THEN LEFT (asset.asset_name, charindex('(', asset.asset_name) - 1) ELSE asset.asset_name END AS VARCHAR(1000)) AS asset_name,[/code]Mon, 11 Aug 2008 01:35:55 GMTpockeyfanhttp://www.sqlservercentral.com/Forums/Topic548922-8-1.aspxI would be inclined to use a function. Something like:[code][color="blue"]SET QUOTED_IDENTIFIER, ANSI_NULLS ON[/color] GO[color="blue"]CREATE[/color] [color="blue"]FUNCTION[/color] dbo.GetAssetNames[color="gray"]([/color]&nbsp&nbsp&nbsp&nbsp@policy_number [color="blue"]varchar[/color][color="gray"]([/color]20[color="gray"])[/color] [color="#008000"]-- or whatever the datatype of policy_number is.[/color][color="gray"])[/color][color="blue"]RETURNS[/color] [color="blue"]varchar[/color][color="gray"]([/color]8000[color="gray"])[/color][color="blue"]AS[/color][color="blue"]BEGIN[/color]&nbsp&nbsp&nbsp&nbsp[color="blue"]DECLARE[/color] @AssetNames [color="blue"]varchar[/color][color="gray"]([/color]8000[color="gray"])[/color]&nbsp&nbsp&nbsp&nbsp[color="blue"]SET[/color] @AssetNames = [color="red"]''[/color]&nbsp&nbsp&nbsp&nbsp[color="blue"]SELECT[/color] @AssetNames = @AssetNames&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+ [color="blue"]CASE[/color]&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp[color="blue"]WHEN[/color] [color="fuchsia"]CHARINDEX[/color]([color="red"]'('[/color], asset_name[color="gray"])[/color] > 0&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp[color="blue"]THEN[/color] [color="fuchsia"]LEFT[/color][color="gray"]([/color]asset_name, [color="fuchsia"]CHARINDEX[/color][color="gray"]([/color][color="red"]'('[/color], asset_name[color="gray"])[/color] - 1[color="gray"])[/color]&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp[color="blue"]ELSE[/color] asset_name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp[color="blue"]END[/color]&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp+ [color="red"]'#'[/color]&nbsp&nbsp&nbsp&nbsp[color="blue"]FROM[/color] asset&nbsp&nbsp&nbsp&nbsp[color="blue"]WHERE[/color] policy_number = @policy_number&nbsp&nbsp&nbsp&nbsp[color="blue"]RETURN[/color] [color="fuchsia"]LEFT[/color][color="gray"]([/color]@AssetNames, [color="fuchsia"]LEN[/color][color="gray"]([/color]@AssetNames[color="gray"])[/color] - 1[color="gray"])[/color][color="blue"]END[/color]GO[color="blue"]SELECT[/color]&nbsp&nbsp&nbsp&nbsppolicy_number&nbsp&nbsp&nbsp&nbsp,ulp_order_id&nbsp&nbsp&nbsp&nbsp,dbo.GetAssetNames[color="gray"]([/color]policy_number[color="gray"])[/color] [color="blue"]AS[/color] asset_name[color="blue"]FROM[/color] ulp_order[/code]Fri, 08 Aug 2008 04:10:58 GMTKen McKelveyhttp://www.sqlservercentral.com/Forums/Topic548922-8-1.aspxHi, here is my problem: As the following codes shown, I want to retrieve some data from several tables to a temprary table "#tmp_437", the problem is "asset_name" could not store two or more "asset.asset_name" under one condition: [quote]select uo.policy_number, uo.ulp_order_id,case when charindex('(', asset.asset_name) > 0 then left(asset.asset_name, charindex('(', asset.asset_name) - 1) else asset.asset_nameend asset_name,...................... into #tmp_437from ulp_order uo, asset,............where ........ [/quote]For one "ulp_order_id", there may be several "asset_name" assigned. I want to get all the "asset.asset_name" and store them into "asset_name": [quote]declare policy_product_cur cursor forselect uo.policy_number, case when charindex('(', a.asset_name) > 0 then left(a.asset_name, charindex('(', a.asset_name) - 1) else a.asset_name end asset_namefrom ulp_order uo, asset awhere uo.product_id = a.asset_id and uo.order_type = 'R'order by ulp_order_idfor read onlyopen policy_product_curfetch policy_product_curinto @tmp_policy_number, @tmp_asset_name while @@sqlstatus != 2begin if @@sqlstatus = 1 begin return end update #tmp_437 set asset_name = asset_name + '#' + @tmp_asset_name where #tmp_437.policy_number = @tmp_policy_number fetch policy_product_cur into @tmp_policy_number, @tmp_asset_name end[/quote]I add a "#" between two "asset_name" for late use in Crystal Report. The problem is "asset_name" is not longer enough to store all the "asset.asset_name". For example, for a certain ulp_order_id, the "asset_name" should be "Premier Life Critical Illness Protector#Hospital Income Benefit", however, the result I get is "Premier Life Critical Illness Protector#Hospital Income Bene", it could not store all the characters. In table "asset", the "asset_name" is varchar(60), I'm afraid when I do select at the beginning, "asset_name" is also defaultly declared as varchar(60), so it could not store variables longer than 60 characters. So is there any way to declare "asset_name" as varchar(100) during "select"? Or is there any simple way to solve this problem? I tried to "create table #tmp_437" before "select" and decalre "asset_name" as varchar(100), however, error message always shown as "Attempt to insert NULL value into column 8 in work table (table id -xxxxxx)"...There will be a lot of things need to be modified... So I ask for some simple methods to store all the "asset.asset_name" without using "create table...". Thanks a lot!Fri, 08 Aug 2008 02:06:07 GMTpockeyfan