SQL Query to Bring Last Letter in a String to First Letter Position

regular expression to find last letter and first letter of string

Here first letter identification is working fine but last letter regular expression is not working

That’s most likely because the last letter is not preceded by a literal ^ character ...

^ and $ only anchor the pattern to the beginning/end, when they themselves appear at the beginning or end of the pattern (broadly speaking). For *.^[aeiou]$ that is not the case for the ^ though, so in this place it means a literal ^ only.

^[aeiou] and [aeiou]$ should be enough here - this demands that one of those vowel characters be found immediately at the beginning (^) resp. end ($). You don’t need the rest that tries to check for arbitrary characters in between.

How to get first character of a string in SQL?

LEFT(colName, 1) will also do this, also. It's equivalent to SUBSTRING(colName, 1, 1).

I like LEFT, since I find it a bit cleaner, but really, there's no difference either way.

How to select a substring but leaving out the last character if is an alphabet in SQL

Find a position of the rightmost digit and take left symbols of the string including this position

DECLARE @CUSTOMER_ID1 VARCHAR(50);
SELECT @CUSTOMER_ID1 = 'RS/123/2017SA';

select left(@CUSTOMER_ID1, len(@CUSTOMER_ID1) - patindex('%[0-9]%', reverse(trim(@CUSTOMER_ID1))) + 1) customer_ID; 

check if first and last character of each word in a string is same

You could create a Split-Function, then it's relatively easy:

SELECT * FROM dbo.TableName t
WHERE NOT EXISTS
(
   SELECT 1 FROM  dbo.Split(t.Col, ' ')x
   WHERE LEFT(x.Item, 1) <> RIGHT(x.Item, 1)
)

Demo

Here's one that i use:

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @ItemTable TABLE (Item VARCHAR(250))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @ItemTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END  

How to replace first and last character of column in sql server?

You can use SUBSTRING for that:

SELECT
    SUBSTRING(col, 2, LEN(col)-2)
FROM ...

Obviously, an even better approach would be not to put leading and trailing commas there in the first place, if this is an option.

I want to remove last and first comma only if exist otherwise not.

The expression becomes a little more complex, but the idea remains the same:

SELECT SUBSTRING(
    col
,  CASE LEFT(@col,1) WHEN ',' THEN 2 ELSE 1 END
,  LEN(@col) -- Start with the full length
             -- Subtract 1 for comma on the left
      - CASE LEFT(@col,1) WHEN ',' THEN 1 ELSE 0 END 
             -- Subtract 1 for comma on the right
      - CASE RIGHT(@col,1) WHEN ',' THEN 1 ELSE 0 END
)
FROM ...

output which has first and last letter as vowel(a,e,i,o,u)

Assuming all the city names are all lower-case letters, you could do something like this:

select city
from   station
where  substr(city,  1, 1) in ('a', 'e', 'i', 'o', 'u')
  and  substr(city, -1, 1) in ('a', 'e', 'i', 'o', 'u')

substr(city, 1, 1) takes the substring of city starting at position 1 and of length 1 (meaning, just the first letter). substr(city, -1, 1) is very similar, just the position is different: -1 means first letter from the end of the string - so this will give you the last letter of the city name.

If city may have both upper and lower case letters, in the WHERE clause use lower(city) instead of city.

EDIT: By popular request, here is how the same can be done with regular expressions. There is no point in using a regular expression approach here though; the standard string functions (like SUBSTR) are almost certain to be much faster than anything based on regular expressions.

....
where regexp_like(city, '^(a|e|i|o|u).*(a|e|i|o|u)$', 'i')

(a|e|i|o|u) means exactly one of those characters. ^ means anchor at the beginning of the string, and similarly $ at the end of the string. Strictly speaking, this requires the city name to be at least two letters long; if one-letter city names are possible, this can be modified easily. (The SUBSTR approach would require no changes.)

The last argument, 'i', makes the regexp matching case insensitive (just in case that is needed).

---

Related Topics