SQL: Trim string at spaces and difference between DBMS, SQL and MySQL

user_10602575 0
Viewed 174

I was practicing on w3schools and used the Customers table (you can select it on the right hand side). As for which DBMS I'm using, I'm not sure. The site says "Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems." - is the syntax between those DBMS the same or very similar?

I want to extract only the first name of the customers. I thought this should do the trick:

SELECT LEFT(ContactName, CHARINDEX(' ',ContactName)-1) 
FROM Customers;

It doesn't work, however, it returns nothing. As in, it doesn't process it, in the output window it shows me the start page, so there's no error message. I don't really get the actual reason behind it, because when I put CHARINDEX by itself in the SELECT statement it gives me the expected result.

Next question would've been how I deal with multiple spaces (when someone has a middle name), but since I got stuck beforehand I didn't get to this part.

sql
2 Answers

I notice that you are using two different columns in the query: ContactName and CustomerName. Why is that?

I suppose your query should work if written like this:

SELECT
  LEFT(ContactName, CHARINDEX(' ', ContactName) - 1) AS FirstName
FROM
  Customers;

It should also work using SUBSTRING, as follows:

SELECT 
  SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName) - 1) AS FirstName
FROM 
  Customers;

Hope this helps.

user_33863100

SQL

  1. Sql is a language used to query relational database systems.SQL full form is structured query language.
  2. Sql is like update,delete,find, etc.
  3. sql basically using multithreading concept where oracle using multiprocessor oracle using for high database handling like banking etc.
  4. Sql language is used in oracle as a writing language. 5 .In SQL server there is no transaction control.

MySQL:

  1. MySQL is a also a Database tool itself that uses SQL language. It is open source.
  2. MySQL is weaker in the areas of inserting and deleting data. But it is an excellent choice, for data storage and referencing data.
  3. MySQL is a relational database management system. You can submit SQL queries to the MySQL database to store, retrieve, modify or delete data.
  4. Basically, MySQL is one of many books holding everything, SQL is how you go about reading that book.

DataBase Management System(DBMS):

  1. A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data.
  2. A database management system (DBMS) is a collection of programs that manages the database structure and controls access to the data stored in the database.
  3. DBMSs include MySQL, PostgreSQL,Microsoft SQL Server, Oracle, Sybase and IBM DB2.
  4. Sometimes a DBMS is loosely referred to as a database.

The Syntax Between them are very similar.

Actually your selection of column was wrong you were selecting ContactName by customer name index secondly you don't need to minus its index. This will return first name of customer.

SELECT LEFT(ContactName, CHARINDEX(' ',ContactName)) 
FROM Customers;
user_58664510
Related