October 18, 2019 at 2:51 pm
The table has many columns, two of which are mutually exclusive, LocationNo and assetNo, so for any record only one of these columns contains a value, while the other is null.
It's easy enough to do this: isnull(LocationNo, AsstNo) as LocOrAsset, but I would like to prepend to this 'A:' or 'L:'
Any smart, sneaky ideas folks, mine are all a bit clumsy - thanks in advance
October 18, 2019 at 3:03 pm
Presumably this will work:
COALESCE ('L' + LocationNo, 'A' + AsstNo) AS LocOrAssetI use COALESCE instead of ISNULL - but that's just my preference. You will need to cast the two columns as varchar or nvarchar if they have a numeric data type.
John
October 18, 2019 at 3:16 pm
That looks like it should - simple - why didn't I think of it!
It's a case of not seeing the wood from the trees because I was going down a CASE route!
I don't have SQL Server at home so I'll try that out next week - many thanks
(is there a free SQL Server I can use on the web by any chance?) (I'll Google for it)
October 19, 2019 at 3:12 pm
I think this will do nicely - a little bit of test code I tried at here:
https://rextester.com/l/sql_server_online_compiler
DECLARE
@AssetNo AS nvarchar(12) = 'M123',
@Location as nvarchar(12) = null,
@ID AS INT = 42;
SELECT ISNULL('L:'+@Location, 'A:'+@AssetNo) as AstorLoc,
SUBSTRING(ISNULL('L:'+@Location, 'A:'+@AssetNo),1,1) as Type,
SUBSTRING(ISNULL('L:'+@Location, 'A:'+@AssetNo),3,99) as AorLNo,
@ID as ID;
set @AssetNo = null;
set @Location = 'MX150';
set @ID = 42;
SELECT ISNULL('L:'+@Location, 'A:'+@AssetNo) as AstorLoc,
SUBSTRING(ISNULL('L:'+@Location, 'A:'+@AssetNo),1,1) as Type,
SUBSTRING(ISNULL('L:'+@Location, 'A:'+@AssetNo),3,99) as AorLNo,
@ID as ID;
And it produces this:
=================================
AstorLoc Type AorLNo ID
A:M123 A M123 42
L:MX150 L MX150 42
==================================
So I can use the above columns on my web page in a few different ways - it may be useful to have two columns with the type and column value as separate values, because I use the value as a hyperlink to drill down to it in subsequent forms, and I need to know what I'm drilling down to, a Location or an Asset because it will use different queries.
October 21, 2019 at 8:01 pm
You might want to consider assigning an alias to the modified value, so that if it ever changes later the definition of it is only in 1 place:
SELECT AstorLoc, LEFT(AstorLoc, 1) AS Type, SUBSTRING(AstorLoc, 3, 99) AS AorLNo, ID
FROM ( VALUES
(CAST('M123' AS nvarchar(12)),CAST(NULL AS nvarchar(12)), CAST(42 AS int)),
(NULL, 'MX150', 42)
) AS data(AssetNo, Location, ID)
CROSS APPLY (
SELECT ISNULL('L:'+Location, 'A:'+AssetNo) as AstorLoc
) AS assign_aliases1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 24, 2019 at 3:22 pm
(is there a free SQL Server I can use on the web by any chance?) (I'll Google for it)
I don't know of any online servers that you can use but if you have a machine you can run it on then this link might be useful to you https://www.microsoft.com/en-au/sql-server/sql-server-downloads
October 24, 2019 at 6:14 pm
Thanks for that, but I found this:
https://rextester.com/l/sql_server_online_compiler
Works really well 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply