CODEWITHSUNDEEP
sqlsql-serverdatabaseoracle-database
Jay
Jay

Reputation: 21

SQL for string more than 255 chars

I am trying to find a way to manipulate values that are returned as part of a query. Basically, if the value is less than 255 char, use this value but if value is more than 255 characters, need to return a string "value more than 255 characters" instead of actual value. I need to achieve this a part of SQL query. Appreciate any feedback.Thanks Jay

Upvotes: 1

Views: 3477

Answers (3)

DavidG
DavidG

Reputation: 118937

Assuming SQL Server, try this which uses the LEN function combined with a CASE:

SELECT 
    CASE WHEN LEN(StringColumn) > 255 
        THEN 'value more than 255 characters' 
        ELSE StringColumn 
    END As MyColumnName
FROM MyTable

Example sqlfiddle: http://www.sqlfiddle.com/#!6/e0508/1/0

Edit: In case you are using Oracle as the tags suggest, instead of LEN, use LENGTH:

SELECT 
    CASE WHEN LENGTH(StringColumn) > 255 
        THEN 'value more than 255 characters' 
        ELSE StringColumn 
    END As MyColumnName
FROM MyTable

Example sqlfiddle: http://www.sqlfiddle.com/#!4/9bc74/2/0

Upvotes: 1

Don Scott
Don Scott

Reputation: 3457

The following CASE pattern will work with most systems:

SELECT
  CASE WHEN LENGTH(someCol) <= 255 
    THEN someCol
    ELSE "value more than 255 characters"
  END AS ColName 
FROM
  TableName

Take note that string functions differ depending on your database software.

See:

Upvotes: 1

numX
numX

Reputation: 850

You can use the LEN() function to determine the length of the string. Then use a CASE conditional structure to determine what value to return.

ref: https://msdn.microsoft.com/en-us/library/ms190329.aspx

Upvotes: 0

Related Questions