Remove number and decimals in a string

[Shriram_Sridhar]

Hi All,

I have a requirement where I need to remove numbers and decimals from a string. The string contains a list of software names and their version number.

Eg:

Adobe Acrobat read x64 12.1.3.4
1tools 6.5.11
Windows 7 Sp3 3.1

I know I can use keepchar() function but it removes all numeric values in the above string. My requirement is only to remove the version numbers.
The result should be;

Adobe Acrobat read x64 
1tools 
Windows 7 Sp3 

Thanks in advance!

[MayilVahanan]

Hi @Shriram_Sridhar 

Try like below

LOAD *,Left(software, index(software, ' ', SubStringCount(software, ' '))-1) as software1 Inline
[
software
Adobe Acrobat read x64 12.1.3.4
1tools 6.5.11
Windows 7 Sp3 3.1
];

output:

[Shriram_Sridhar]

Hi Mayil Vahanan,

Thanks for the solution. It helped me to exclude the last word in the string. I have another scenario where it does not work.

Eg:
NI Xerces Delay Load 2.7.7 64-bit - here the version number is between the string.
How should I handle both these cases? is there a function that can eliminate a number and decimal format like ##.##.## 

Thanks for your time help ! 🙂 

[Kushal_Chawda]

@Shriram_Sridhar  Regular expression will be useful here and will be better approach here. You can use regular expression connector which is pat of the web connector package. Look at below

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-So...

But you can try below. Assuming '.' is used with version number only and not used in any other text. Otherwise this logic won't work.

LOAD *,
     trim(replace(Software,mid(Software,Index(Software,'.')-2,(Index(Software,'.',-1)+2)-(Index(Software,'.')-2)),'')) as Software_new;
load * Inline [
Software
NI Xerces Delay Load 2.7.7 64-bit
Adobe Acrobat read x64 12.1.3.4
1tools 6.5.11
Windows 7 Sp3 3.1
]