sql server – Comma separated value string and compare against other values

Hey all I have been trying to wrap my head around how to go about doing this for a while now and I’m not really coming up with anything.

Here is my query:

DECLARE @ATID AS VARCHAR(3) = 12
DECLARE @AssetID AS VARCHAR(7) = 306

SELECT 
    tblXDataA.AssetID AS 'LID',
    tblA.AssetID AS 'AID',
    tblEM.(Custom) AS 'ABC/S XML',
    tblEMXData.(Custom) AS 'XData XML',
    tblEM.(Custom).value('(/ABCS/text())(1)', 'varchar(MAX)') AS 'Parsed XML -> ABCS',
    tblEM.(Custom).value('(/ABC/text())(1)', 'varchar(MAX)') AS 'Parsed XML -> ABC',
    tblEMXData.(Custom).value('(/XData/ABC/text())(1)', 'varchar(MAX)') AS 'Parsed XML -> XData'
FROM 
    Asset AS tblA 
JOIN 
    LinkStr AS tblL 
        ON tblL.A_AssetID = tblA.AssetID 
JOIN 
    Asset AS tblXDataA 
        ON tblXDataA.AssetID = tblL.B_AssetID 
JOIN
    EMd AS tblEMXData
        ON tblEMXData.AssetID = tblXDataA.AssetID
JOIN 
    EMd AS tblEM 
        ON tblEM.AssetID = tblA.AssetID 
JOIN 
    LinkStr AS tbLinkStrB 
        ON tblXDataA.AssetID = tbLinkStrB.B_AssetID 
WHERE 
    tblXDataA.ATID = @ATID 
AND 
    tblA.AssetID = @AssetID;

All of that above produces a few columns of output but the main columns are the ABCS and XData:

LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData                  
------------------------------------------------------------------------------------------------------------
481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240
501 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| 05649880-ED43

The above is correct as-is. There are 2 users and therefore the ABCS has a comma delimiter with both users ABCS. The first user that is added is the main user that all other users go under and therefore it has the same userID for both (306).

The users ABCS are:

User 1 (main) : Y40GB4DL4-48302343420105-FH3J-8798-11240

User 2 (addition): 05649880-ED43

However, If I delete the last user added (05649880-ED43) then ABCS should only have 1 users ABCS in it instead of 2. But this is how its turning out:

LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData                  
------------------------------------------------------------------------------------------------------------
481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240

So depending on how many users are added to the main user is what I need to check for to make sure those users who get deleted are not left in the ABCS comma string.

Needing to:

Get each ABCS comma delimited value and compare it to XData(s)

So as the example above it would flow like this taken that the database looks like this:

LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData                  
------------------------------------------------------------------------------------------------------------
481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240
501 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| 05649880-ED43

Is Y40GB4DL4-48302343420105-FH3J-8798-11240 (ABCS) present in (XData)? answer: true

Is 05649880-ED43 (ABCS) present in (XData)? answer: true

Since there are 2 users and 2 rows then that would, of course, return both as TRUE and that would be all.

Now If the added user gets deleted from the main user. The table would look like this and which is what currently is happening:

LID | AID | Parsed XML -> ABCS                                    | Parsed -> XData                  
------------------------------------------------------------------------------------------------------------
481 | 306 | Y40GB4DL4-48302343420105-FH3J-8798-11240,05649880-ED43| Y40GB4DL4-48302343420105-FH3J-8798-11240

Is Y40GB4DL4-48302343420105-FH3J-8798-11240 (ABCS) present in (XData)? answer: true

Is 05649880-ED43 (ABCS) present in (XData)? answer: false

Since its true and false then the false users ABCS ID needs to be taken out (DELETED/REMOVED) of the comma separated string. This is what I am unable to find out how to go about doing.

Help would be great!

database design – Insert JSONL with two columns of comma separated fields into separate rows in mysql

I have json datas in the format as shown below.

**the number of objects under ‘images'(VIEWS, ROOMS, etc.) varied for each JSON object. Not all objects have VIEWS…POOL, it may only have ROOMS.

    "propertyId":{"ea":"12345678","h":"","vo":""},
    "thumbnail":{"width":1000,"height":500,"link":"xxx.jpg"},
    "hero":{"title":"Featured Image","width":1000,"height":669,"link":"yyy.jpg"},
    "images":{
       "VIEWS":(
        {"title":"View from Property","width":1000,"height":666,"link":"a1.jpg"},
        {"title":"View from Property","width":1000,"height":666,"link":"a2.jpg"}
       ),
        "ROOMS":(
            {"title":"Room","width":1000,"height":669,"link":"b1.jpg"},
            {"title":"Room","width":1000,"height":662,"link":"b2.jpg"}
       ),
        "EXTERIOR":(
            {"title":"Terrace","width":1000,"height":666,"link":"c1.jpg"},
            {"title":"Terrace","width":1000,"height":666,"link":"c2.jpg"}
       ),
        "AMENITIES":(
             {"title":"Property Amenity","width":1000,"height":666,"link":"d1.jpg"}
       ),
        "POOL":(
            {"title":"Outdoor Pool","width":1000,"height":666,"link":"e1"},
            {"title":"Outdoor Pool","width":1000,"height":666,"link":"e2.jpg"}
    )}
    }

I wanted to insert the following data into the table with the following format in MySQL Ver 8.0.23 database.
enter image description here

JSON -> Table Reference

    HotelId = propertyId.ea
    hero_url = hero.link
    hero_title = hero.title
    media_title = images.(VIEWS/ROOMS/EXTERIOR/...).title 
    media_url = images.(VIEWS/ROOMS/EXTERIOR/...).link
    >> ...other objects may have different category (VIEWS/ROOMS...)
    created_at and updated_at = date

After searching through the old topics, I was able to use the select statement below to display the format above. However, I’m confused as to it how works when using INSERT.

  SELECT t.HotelID, t.hero_url, t.hero_title,SUBSTRING_INDEX(SUBSTRING_INDEX(t.media_title, ',', n.n), ',', -1) media_title, SUBSTRING_INDEX(SUBSTRING_INDEX(t.images, ',', n.n), ',', -1) images
  FROM hotel_sample.property_photos t CROSS JOIN 
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n 
   ) n
 WHERE n.n <= 1 + (LENGTH(t.media_title) - LENGTH(REPLACE(t.media_title, ',', '')))
 ORDER BY HotelID

Would appreciate if anyone could show me how I could insert the above JSON data into the table. (MySQL Ver 8.0.23)

CREATE TABLE `property_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `HotelID` varchar(10) DEFAULT NULL,
  `hero_url` varchar(255) DEFAULT NULL,
  `hero_title` text,
  `category` varchar(30) DEFAULT NULL,
  `media_url` varchar(255) DEFAULT NULL,
  `media_title` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY (`id`)

seo – Is there way to force Google to think that sub domain as totally separated are from main domain?

Google consider a sub domain as a completely different website for his domain. So if your domain rank well in “cars (for example) a sub domain can rank well in “pets care”.

But Google still know if 2 or more website are related even if them are on different domains and different servers, easier if it is a sub domain.

So as from the comment of Stephen Ostermiller if you do something wrong will be partially reflect on the main domain, as well as if you do something good.

If you want to keep a clear image and easy navigation system on your main site without mess up with link between subdomains and tools and blogs or whatever, I can suggest you to have only 2 domains (so not so expensive): 1 is the main one, for your web design agency, the other one you can use for tools and experiment and anything you like. This not for Google but also for your pace of mind that keep separate the “experiment” (for example maybe in the future you will shut down a tool).
example:

main site: yourmaindomain.com

other services:
yourseconddomain.com/imagehostingservice

yourseconddomain.com/seotools

yourseconddomain.com/calculator

And keep them in 2 different cPanel, not for SEO reason but to keep easy the navigation of the main website without using subdomain that maybe point to folder also reachable from other URL (example: you have a subdomain -sub.example.com- that point to public_html/subdomainfolder. That folder you can reach by sub.example.com but also from example.com/subdomainfolder and this can generate confusion when search engine try to map your site.

Then you can link to each other some pages if need.

mg.metric geometry – Choosing maximum number of separated points on a sphere surface

The following problem came up in one of my research works. Suppose that $C$ denotes the positive face of the $d$-dimensional unit sphere surface, i.e.
$$C := {mathbf{x} in mathbb{R}^d: x_1 >0,ldots,x_d>0, |mathbf{x}|_2 =1}~.$$

I am given a fixed real number $rho > 0$. Then, how many points can I have at maximum on $C$, such that all their pairwise inner products are bounded below by $rho$? In other words, I am trying to fill up $C$ in a most crowded manner, such that the points are separated enoungh.

It seems to me that there should be some well known result in Mathematics about this, but I cannot figure it out. For my research purpose, I would be happy if the maximum number of such possible points is of the order $e^{mathrm{poly}(d)}$, and if so, I would be grateful if someone can tell me the exact degree of this $mathrm{poly}(d)$, assuming $rho$ is fixed.

javascript – Should abilities have their state and functionality separated?

I’m developing a top-down game using Javascript and Canvas in an ECS architecture.

I’m wondering, which of these is better from a design / elegance point of view?

Method 1: Combining the ability function and its metadata into one object:

// in ability factory
createBlinkAbility() {
  return {
    cooldown: 5000,
    castTime: 1000,
    hotkey: "q",
    execute(entity: Entity, scene: Scene) {
      let position = entity.get(CT.Position);

      let level = scene.queryComponent(CT.Level);

      position.x = Helpers.randomInt(0, level.width);
      position.y = Helpers.randomInt(0, level.height);
    }
  }
}

function executeCurrentCast(entity: Entity, ability: Ability) {
  ability.execute(entity); // all abilities have an execute function
}

Method 2: Separating ability metadata from its function:

// in ability factory
createBlinkAbility() {
  return {
    type: "blink",
    cooldown: 5000,
    castTime: 1000,
    hotkey: "q"
  }
}

// in ability factory
castBlink = (entity: Entity, scene: Scene) => {
  let position = entity.get(CT.Position);

  let level = scene.queryComponent(CT.Level);

  position.x = Helpers.randomInt(0, level.width);
  position.y = Helpers.randomInt(0, level.height);
}

function executeCurrentCast(entity: Entity, ability: Ability) {
  switch (ability.type) {
    case "bow": this.abilityFactory.castBow(entity); break;
    case "blink": this.abilityFactory.castBlink(entity); break;
    ...
  }
}

I know in general in an ECS architecture it is wise to separate “state” from “actions”, but I’m not sure if this would also apply to things like abilities. It seems like it might be wise to maintain that separation, but the code seems like it might be “cleaner”, or shorter at least, in the former case.

Lastly, I’m not really concerned with the performance differences between these two approaches, but rather which is better from a design standpoint.

sharepoint online – MS Flow iterate “;” separated values

I am using SharePoint online as a platform where I have a custom form which has multiselect dropdown control. I’m saving the values of the control using “;” in the single line of text field.

Now I want to iterate each of the values which are saved in the field using MS Flow and for example if there are 4 values such as “abc;xyz;hi;hello”, I then want these values to be inserted in another list with each value being an item (row) with title column.

Any help on this would be appreciated.

I want to fetch Product Names on behalf of comma separated SKU’s

I want to fetch Product Names on behalf of comma separated SKU’s i.e. sku1,sku2,sku3 and the expected result is Name1,Name2,Name3

Best practice for comma separated input size for the search field

So to reiterate:

  • Users have .csvs or other files where large numbers of IMEis are listed
  • They need to be able to search for these IMEIs in your system

Ideally you’d have access to analytics or user interviews that could help you define the upper limit users search. It sounds like you don’t have access to either, so in the meantime we can make a few assumptions.

As you said, it seems the most likely scenario is that they’ll be copying and pasting these numbers, as they are difficult to correctly input due to their length. They likely won’t be checking their work, again due to length, so displaying the pasted content is mostly irrelevant – you can display “1234567890abcde, 1234567890abcde, and 498 more“, which should give them enough information about their search to complete their task.

The main bottleneck will likely be your backend system, not the UI. If you paste 500 IMEIs, how fast does the system respond? If it slows at any point and effects the UX, you’ve found your limit. If it responds adequately for 10,000 IMEIs, there’s little reason to limit it at all.

Search field comma separated input size best practice

In case working with large number of entries in web UI such as IoT device list with IMEI. And in case there is a need to make quick multiple selection by IMEI.
Would it be good practice to provide search field with possibility to enter comma separated IMEIs?
In most cases those IMEIs will be pasted into search field.

What would be sane maximum limit for such input?
50 IMEIs (850 symbols), 100 IMEIs (1700 symbols), even more?
What is main bottle neck for such solution?

I understand CSV file import could be used, but we want quicker solution.

build – Building angular custom elements into separated bundles where all vendors (dependencies) are placing in a separate file

For example, I have 2 bundles with custom elements: a.js, b.js

For each element I have own compiler (a-main.ts, b-main.ts) which bootstraps the module I need (AModule.ts, BModule.ts).

{a,b}.js is result of: runtime.js and main.js.
Then I have common.js, which is result of vendors.js and pollyfils.js of each build of custom elements.

As result, I have 2 files with custom elements and 1 file with pollyfills and vendors with own module scope for each custom element (fst scope for a, scnd scope for b).

Is there any solution how I can exclude duplicated code of dependencies and angular core from common.js?