SQL Express/SQL Server

[madtowng]

Hi everyone,
I find myself force to update and maintain some files using MSSQL instead of my much preferred MySQL.
I created the code below (that gets the job done), but I'm wondering if there's a better way to write the sql.

Any help will be a blessing.

Gregg

select userid,lname from [user]
where  left(lname,1) between '0' and '9' and convert(varchar(5),convert(int,userid)) = lname

[WayneH]

Hi Gregg,

I have only used MSSQL and have found most of the syntax to be portable.
I would have probably approached it the same way you did. While likely not applicable, if you determine your data could have a row without a number value in the lname column, first pos, it could cause an issue. If that’s possible, you could use try_cast and it will return null if it cannot parse the column.

WITH FilteredUsers AS (
  SELECT userid, lname
  FROM [user]
  WHERE LEFT(lname, 1) BETWEEN '0' AND '9'
)
SELECT *
FROM FilteredUsers
WHERE TRY_CAST(userid AS INT) IS NOT NULL AND TRY_CAST(userid AS VARCHAR(5)) = lname;​

Wayne

[madtowng]

Hi Wayne,
Thank you for responding.
The userid column is 10 characters long, using zeros to left pad the id,
so I trying to filter rows where userid '0000000245' and lname = '245'.

trycast looks like an interesting function. I'm trying to figure out what the With FilteredUsers as does (Is it similar to mySql's <calculated value> as 'FilteredUsers'

[WayneH]

I think I see what you are doing. One table I assume has the ID with leading zeros and the related table does not?
I am completely unfamiliar with MYSQL. I probably need to learn it as it would get me away from express.
The WITH FilteredUsers is basically a CTE - Common Table Expression. Basically it is just a syntax thing to keep it clean. In SQL, it would be like putting a calculation into a function and then calling that function or variable from another set of code.
It becomes more efficient if you had another part of the query that needs to call the same records. It saves you from typing it over again and you can just call the CTE in your select.

I am not a pro on it and end up fighting with dates and numbers alot... but I think you might be able to use CAST in this case as well. Maybe worth trying.

WITH FilteredUsers AS (
  SELECT
    userid,
    lname,
    -- Remove leading zeros from userid by converting it to an integer and back to string
    CAST(CAST(userid AS INT) AS VARCHAR(50)) AS stripped_userid,
    CAST(CAST(lname AS INT) AS VARCHAR(50)) AS stripped_lname
  FROM [user]
  WHERE LEFT(lname, 1) BETWEEN '0' AND '9'
)
SELECT *
FROM FilteredUsers
WHERE TRY_CAST(stripped_userid AS INT) IS NOT NULL AND stripped_userid = stripped_lname;
​

[kkfin]

?
I am completely unfamiliar with MYSQL. I probably need to learn it as it would get me away from express.

If you or your customers have any need for MS365 integration then SQL Server or SQL Database is the right choice. You can easily integrate your application to almost any MS365 application for example using Power Automate. This may be a big advantage over MYSQL.

[madtowng]

Thanks Wayne.
Im going to persue this just because its not uncommon to
run across mssql, but if you want
to mess with mysql(including functions to make working with dates easier, just reach out. The date functions are easy on
their own, but since i have a habit of forgetting of
the format or the value comes first, I wrote my own functions that just need the value (ie: gl.today() gets 2023-10-05).

[madtowng]

kkfin I thought microsoft provides various connectors to 3rd party products (like MySQL and X(formerly known as twitter).
I still have to experiment with power automate

[kkfin]

kkfin I thought microsoft provides various connectors to 3rd party products (like MySQL and X(formerly known as twitter).
I still have to experiment with power automate

Yes they do but as i did put in my post in bold you can build easily with SQL Server. SQL Server connector has lot of prebuild triggers. With MYSQL you are totally on your own.

[TheDevilIsMe]

try this

SELECT u.userid, u.lname FROM [user] u JOIN (SELECT DISTINCT userid FROM [user] WHERE LEFT(lname, 1) BETWEEN '0' AND '9') d ON d.userid = CAST(u.lname AS INT)

[Russell Craycraft]

I have found ChatGPT very useful in learning more about SQL. For instance, I put the above query into it and got.

SELECT u.userid, u.lname FROM [user] u WHERE u.lname LIKE '[0-9]%';

The big thing with ChatGPT is learning how to write the questions. I started out getting responses that didn't quite fit, but you have to order it around a little bit, and be direct.

image.png


and for your original code:

SELECT userid, lname
FROM [user]
WHERE lname LIKE '[0-9]%' AND ISNUMERIC(userid) = 1 AND CAST(userid AS INT) = CAST(lname AS INT);

image.png

[madtowng]

Hi Russell,
Thanks to ms sql, I stumbled across having chatGPT "fix" my code.
I think this is an option I'm probably going to exploit a lot in the future.

[TheDevilIsMe]

Hi Russell,
Thanks to ms sql, I stumbled across having chatGPT "fix" my code.
I think this is an option I'm probably going to exploit a lot in the future.

you will be amazed of how many AI brands are available, I use

• Perplexity AI.
• Google Bard AI.
• Chatsonic.
• GPT
• LILMAand

And so many other online tools, which can even do the entire app and deploy it for you with the best practise and security standards.
but at the end, it will not do these small details which human brains can achive!

Good luck Gregg :)

[WayneH]

Would you like to play a game?
In all seriousness, agree… ai is going to be revolutionary. Especially useful when we can start seeing it integrated into our code editor.

[TheDevilIsMe]

Would you like to play a game?
In all seriousness, agree… ai is going to be revolutionary. Especially useful when we can start seeing it integrated into our code editor.

Indeed, it's true that AI greatly assists programmers. However, it's crucial to remember a fundamental aspect – AI is a product of human ingenuity. While this may delve into philosophical perspectives, my life experiences have shown that humans possess a complex spectrum of qualities, encompassing both greed and selflessness, intelligence and folly, goodness and malevolence. This inherent duality is woven into our design.

The key takeaway is that AI, despite its immense capabilities, will always harbor vulnerabilities. Beyond the obvious concerns of cybersecurity, we must acknowledge that its designers, being human, introduce potential weak points. There will invariably exist avenues to access and manipulate AI when circumstances demand it.

Also, do not forget networks, network, intranets, vpns, internet zones and intenet caching will always be over any application in the world.

Please take heed of these words.and mark it!

[madtowng]

how can anyone not like the wargames reference.