select first alphabet

Question

Post reply

select first alphabet

kapil_kk

SSC-Insane

Points: 21316
More actions
March 19, 2013 at 12:27 am

#269906

i have a column in my table which has values like 'value Based', 'value Discount', 'Percentage Discount'.
I want to extract first letter from these words as 'VB', 'VD', 'PD'.
How to achieve this?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AJ@SQL SSC Eights! Points: 811 More actions · Answer 1

Couple of Ways you can try:

SELECT LEFT(COLNAME,1)+LEFT(LTRIM(RIGHT(COLNAME,LEN(COLNAME)-CHARINDEX(' ',COLNAME,1))),1) FROM TESTCOL

SELECT LEFT(COLNAME,1)+SUBSTRING(COLNAME,CHARINDEX(' ',COLNAME,1)+1,1) FROM TESTCOL

You may want to play further with the functions 🙂

Prasad.N Ten Centuries Points: 1083 More actions · Answer 2

hi

find below code

declare @retval varchar(100)

DECLARE @STR VARCHAR(100);

set @STR='Nyal Pra Raj'

SET @STR=RTRIM(LTRIM(@str));

SET @retval=LEFT(@str,1);

WHILE CHARINDEX(' ',@str,1)>0 BEGIN

SET @STR=LTRIM(RIGHT(@str,LEN(@str)-CHARINDEX(' ',@str,1)));

SET @retval+=LEFT(@str,1);

END

select @retval;

Prasad.N
Hyderabad-India.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Prasad.N (3/19/2013)
hi
find below code
declare @retval varchar(100)
DECLARE @STR VARCHAR(100);
set @STR='Nyal Pra Raj'
SET @STR=RTRIM(LTRIM(@str));
SET @retval=LEFT(@str,1);
WHILE CHARINDEX(' ',@str,1)>0 BEGIN
SET @STR=LTRIM(RIGHT(@str,LEN(@str)-CHARINDEX(' ',@str,1)));
SET @retval+=LEFT(@str,1);
END
select @retval;

Thanks Prasad its works 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

kapil_kk (3/19/2013)
i have a column in my table which has values like 'value Based', 'value Discount', 'Percentage Discount'.
I want to extract first letter from these words as 'VB', 'VD', 'PD'.
How to achieve this?

The three strings you've posted each contain two 'words'. What's the maximum number of words any string may contain? There are two good solutions to this, both very fast. Which one to choose depends upon the max number of words found in any one string.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

ChrisM@Work (3/19/2013)
kapil_kk (3/19/2013)
i have a column in my table which has values like 'value Based', 'value Discount', 'Percentage Discount'.
I want to extract first letter from these words as 'VB', 'VD', 'PD'.
How to achieve this?
The three strings you've posted each contain two 'words'. What's the maximum number of words any string may contain? There are two good solutions to this, both very fast. Which one to choose depends upon the max number of words found in any one string.

Currently i have list of two words only so have to work on 2 words only

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

;WITH Sampledata (MyString) AS (

SELECT 'value Based' UNION ALL

SELECT 'value Discount' UNION ALL

SELECT 'Percentage Discount')

SELECT

s.MyString,

Word1Initial = LEFT(s.MyString,1),

Word2Initial = SUBSTRING(s.MyString, x.pos,1)

FROM Sampledata s

CROSS APPLY (SELECT pos = 1 + CHARINDEX(' ', MyString, 0)) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rajarao.m SSC Veteran Points: 251 More actions · Answer 7

create table fstlst(name varchar(max))

insert into fstlst values('raja rao')

insert into fstlst values('mohan raj')

insert into fstlst values('sachin tendulkar')

insert into fstlst values('gowtham gambeer')

insert into fstlst values('veerndar sehwag')

select * from fstlst

select LEFT(name,1)+substring(name,CHARINDEX(' ',name,1)+1,1) from fstlst

select substring(name,1,1)+SUBSTRING(name,CHARINDEX(' ',name,1)+1,1) from fstlst