CODEWITHSUNDEEP
sqloracle-database
Kyujiin
Kyujiin

Reputation: 109

Limit characters retrieved on a certain column from Select query

Inquiring if there is a way to limit the characters of a certain column in a select statement.

ex. SELECT ADDRESS FROM EMPLOYEETABLE;

ADDRESS is of type VARCHAR2(500) but I want to limit the number of characters that it will output to 100.

Upvotes: 0

Views: 1934

Answers (2)

Shakti K
Shakti K

Reputation: 72

To get data of a fixed length from a column in MSSQL or Oracle

Yes, you can do it by using SUBSTRING() function in MSSql and SUBSTR() in Oracle.

For example: Let's say we have a table test_emp with fields name, mobile and emp_address.

MSSQL & Oracle Syntax

create table test_emp(name varchar(30),mobile varchar(10),emp_address varchar(500));

Now insert some data in test_emp table.

MSSQL & Oracle Syntax

insert into test_emp(name,mobile,emp_address)  values('Anonymous','0123456789','Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum');

The length of the given data in emp_address field is 359 To check the length MSSQL Syntax

select len('Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum')

Oracle Syntax

select Length('Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum Lorem Ipsum')

Now you can use the select statement to get the desired output

In MSSQL

select name,mobile,SUBSTRING(emp_address,1,100) from test_emp;

In Oracle

select name,mobile,SUBSTR(emp_address,1,100) from test_emp;

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18640

Use SUBSTR:

SELECT SUBSTR(address,1,100)
  FROM employeetable;

Upvotes: 1

Related Questions