Can a Table Field Contain a Hyphen

Can a table field contain a hyphen?

Yes, you can use punctuation, white space, international characters, and SQL reserved words if you use delimited identifiers:

SELECT * FROM `my-table`;

In MySQL, use the back-ticks. In standard SQL, use double-quotes.

Or if you use MySQL you can set the ANSI_QUOTES SQL mode:

SET SQL_MODE = ANSI_QUOTES;
SELECT * FROM "my-table";

How do I deal with SQL tablenames with hyphen (-) when writing raw queries? i.e project-users

According to http://www.postgresql.org/docs/9.0/static/sql-syntax-lexical.html, you should use double quotes.

In your case, for PostgreSQL the query should be:

SELECT * FROM "project-users";

It is good practice to avoid the use of characters that need escaping or that contain spaces in identifiers.

Adding hyphen in a column in sql table

select concat(substring(value, 1, len(value)-1), '-', substring(value, len(value), 1)) from data;create table data(value varchar(100));

Here is the full example:

create table data(value varchar(100));
insert into data values('6789567');
insert into data values('98765434');
insert into data values('1234567');
insert into data values('876545');
insert into data values('342365');

select concat(substring(value, 1, len(value)-1), '-', substring(value, len(value), 1)) from data;


 | (No column name) |
 | :--------------- |
 | 678956-7         |
 | 9876543-4        |
 | 123456-7         |
 | 87654-5          |
 | 34236-5          |

In case OP meant there can be multiple numbers in the column value here is the solution:

create table data1(value varchar(100));
insert into data1 values('6789567,5467474,846364');
insert into data1 values('98765434,6474644,76866,68696');
insert into data1 values('1234567,35637373');


select t.value, string_agg(concat(substring(token.value, 1, len(token.value)-1), '-', 
substring(token.value, len(token.value), 1)), ',') as result 
from data1 t cross apply string_split(value, ',') as token group by t.value;


value                        | result                          
:--------------------------- | :-------------------------------
1234567,35637373             | 123456-7,3563737-3              
6789567,5467474,846364       | 678956-7,546747-4,84636-4        
98765434,6474644,76866,68696 | 9876543-4,647464-4,7686-6,6869-6

Hyphens in column names in MySQL DB

enclose the names within `back-ticks`

MySQL find value in a separated hyphen list

You should normalize your tables by storing single user_cat value against each user_id. Refer to @Bill Karwin's answer. However, you can still make your current solution work by replacing comma with hyphen in the WHERE clause,

SELECT dash_cat_id,
REPLACE(dash_users_cats, '-', ',') as dashRep
FROM dash_cats
WHERE FIND_IN_SET(2, REPLACE(dash_users_cats, '-', ','))

Create Hive Table with hyphen (-) in Table Name

Actually, table name containing - is not legitimate.

The source code taken from org.apache.hadoop.hive.metastore.MetaStoreUtils shows that only characters, numbers and underscores are allowed in table names:

/**
   * validateName
   *
   * Checks the name conforms to our standars which are: "[a-zA-z_0-9]+". checks
   * this is just characters and numbers and _
   * ...
   */
  static public boolean validateName(String name) {
    Pattern tpat = Pattern.compile("[\\w_]+");
    Matcher m = tpat.matcher(name);
    if (m.matches()) {
      return true;
    }
    return false;
  }

How to match string that may contain hyphen in the where clause?

SELECT Name FROM table WHERE Replace(Number,'-','') = '22233'

---

Related Topics